Wednesday, December 13, 2006

Offline MSWindows Patching

Installing a fresh operating system and connecting it to the Internet to install the latest patches can be risky.

The program available here will download needed patches so they can be put to an ISO CD or DVD. The program even has a simple GUI to choose the download options.

Wednesday, December 06, 2006

Oracle and Tuxedo Transaction Deadlock

Follow these steps to locate the Tuxedo logs of the services that have deadlocked in an Oracle database. Tuxedo service debugging should have already been implemented before the deadlock occurred.

Part of Oracle alert.log:
Mon Dec 4 13:59:36 2006
ORA-000060: Deadlock detected. More info in file /u103/oracle/admin/DB/udump/db_ora_1567.trc.

Optionally confirm time of deadlock:
$ grep 'SESSION ID' db_ora_1567.trc
*** SESSION ID:(176.15362) 2006-12-04 13:59:27.820

Find the Tuxedo program name and machine names. Replace "host" with a string that matches your application server host names.
$ grep host db_ora_1567.trc
Node name: dbhost
Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 10159, machine: app018
program: om_Customer@app018 (TNS V1-V3)
application name: om_Customer@app018 (TNS V1-V3), hash value=0
OSD pid info: Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 12110, machine: app020
program: om_OrderProces@app020 (TNS V1-V3)
application name: om_OrderProces@app020 (TNS V1-V3), hash value=0

On machine app018 go to Tuxedo service log directory:
$ cd log

Find the files with the service and time that were in the database deadlock trace file:
$ ls -otr om_Customer* om_OrderProces*|tail -9
-rw-r--r-- 1 arbor 3733 Dec 4 00:01 om_OrderProcessing-061204-000033.03.10169.server.sql00100
-rw-r--r-- 1 arbor 303300 Dec 4 00:01 om_OrderProcessing-061204-000026.03.10705.server.001
-rw-r--r-- 1 arbor 3378 Dec 4 12:10 om_Customer-061204-120950.03.10159.server.sql00100
-rw-r--r-- 1 arbor 46855616 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.dbg
-rw-r--r-- 1 arbor 5525130 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.001
-rw-r--r-- 1 arbor 882855649 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.dbg
-rw-r--r-- 1 arbor 259093081 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.001
-rw-rw---- 1 arbor 3017100 Dec 4 19:57 om_OrderProcessing.3.err
-rw-rw---- 1 arbor 324921588 Dec 4 19:57 om_Customer.3.err

These tuxedo log files along with the database deadlock trace file can be used to determine the cause and potential resolution of the deadlock condition.

Tuesday, December 05, 2006

Oracle alert.log "ORA-" Monitoring

Oracle DBAs often have a purchased tool or home-grown scripts to monitor the Oracle alert.log for errors.
DBAs will often search the alert.log for lines that begin with "ORA-", even though not all ORA- messages are database errors (user cancelling a long-running will query will create a ORA- message but this is not a database error).

Aside from the fact that all ORA- messages are not errors, not all errors are identified with ORA-. This is a list of some of the messages that do not begin with ORA- that a DBA may be interested in monitoring.

Fri Oct 27 14:35:43 2006
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition


Thu Oct 26 07:11:29 2006
Failure to extend rollback segment 22 because of 30036 condition
FULL status of rollback segment 22 set.


Tue Oct 10 09:57:03 2006
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump


These two messages may be seen once when the instance is starting:
Deprecated system parameters with specified values:

Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid severe performance degradation.


The number 30036 means ORA-30036.
Failure to extend rollback segment 8 because of 30036 conditionFULL status of rollback segment 8 set.

There are a couple ORA- messages for when the archive log destination is full.
Wed Jan 31 21:58:41 2007
ARC1: Evaluating archive log 5 thread 1 sequence 11273
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 5 thread 1 sequence 11273
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Jan 31 21:58:41 2007
ORACLE Instance SID - Archival Error
ARCH: Connecting to console port...
Wed Jan 31 21:58:41 2007
ORA-16014: log 5 sequence# 11273 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u101/oradata/SID/redo05a.log'
ARCH: Connecting to console port...
ARCH:

Monday, November 27, 2006

PC/MSWindows Utilities

system info as wallpaper
BgInfo is now a Microsoft product. There is install program, so place the executable where you won't lose it.

folder size
http://foldersize.sourceforge.net/

mother board monitor
http://www.pcworld.com/downloads/file/fid,7309-order,1-page,1-c,systemresourcestuneup/description.html

control what programs automatically start up
http://www.pcworld.com/downloads/file/fid,7976-order,1-page,1-c,desktop/description.html

intel application accelerator. replaces ata driver.
http://support.intel.com/support/chipsets/iaa/

Intel® Chipset Identification Utility
http://downloadfinder.intel.com/scripts-df-external/Product_Filter.aspx?ProductID=861

Tuesday, November 14, 2006

Tuning Secure File Copy For Performance

When scp is too slow, and rdist over scp is more functionality than you need, look at sftp.

The sftp command set will be familiar to anyone who has used ftp.
Sftp allows use of a batch file, and it has a tunable buffer size. The -B flag controls the tunable buffer size and is useful when copying files over wide links that have latency. Tests over a DS3 have shown 20% throughput increases simply by increasing the default buffer of 32K. There are limits, and you can experiment on your platforms. Choosing a value too high will result in a message such as, "Outbound message too long 262169".

Wednesday, November 08, 2006

Release Tuxedo Connections

If the MAXWSCLIENTS had been reached, additional users can not log in to Tuxedo. One way to release connections in Tuxedo is to kill the WSH processes, then use the tmadmin bbclean command.

$ ps -ef|grep WSH | head
arbor 8133 19874 1 12:09:09 ? 0:00 WSH -c 11 -d /dev/tcp -i 21 -s 647181 -p 2048 -P 65535
arbor 16042 19875 0 Nov 6 ? 0:08 WSH -c 11 -d /dev/tcp -i 2 -s 647182 -p 2048 -P 65535
arbor 18303 19874 0 09:21:09 ? 0:00 WSH -c 11 -d /dev/tcp -i 46 -s 647181 -p 2048 -P 65535
arbor 19369 19874 0 09:28:38 ? 0:00 WSH -c 11 -d /dev/tcp -i 47 -s 647181 -p 2048 -P 65535
arbor 3754 19874 0 11:29:54 ? 0:00 WSH -c 11 -d /dev/tcp -i 8 -s 647181 -p 2048 -P 65535
arbor 5840 19874 0 11:42:43 ? 0:00 WSH -c 11 -d /dev/tcp -i 2 -s 647181 -p 2048 -P 65535
arbor 10810 19874 0 12:55:25 ? 0:01 WSH -c 11 -d /dev/tcp -i 44 -s 647181 -p 2048 -P 65535
arbor 10565 19874 0 Nov 6 ? 0:04 WSH -c 11 -d /dev/tcp -i 15 -s 647181 -p 2048 -P 65535
arbor 19989 19874 0 Nov 5 ? 0:04 WSH -c 11 -d /dev/tcp -i 0 -s 647181 -p 2048 -P 65535
arbor 7109 19874 2 Nov 6 ? 0:04 WSH -c 11 -d /dev/tcp -i 33 -s 647181 -p 2048 -P 65535

$ $ kill -kill 19989

$ tmadmin
tmadmin - Copyright (c) 1996-1999 BEA Systems, Inc.
Portions * Copyright 1986-1997 RSA Data Security, Inc.
All Rights Reserved.
Distributed under license by BEA Systems, Inc.
Tuxedo is a registered trademark.

> bbclean
Cleaning the bulletin board on machine FX.

> quit


Count the current tuxedo connections (including the internal users)
$ tuxedo8.0/bin/tmadmin 2>/dev/null << EOF | /usr/bin/grep ^FX | /usr/bin/wc -l
printclient
quit
EOF

Monday, November 06, 2006

Capture and Transfer Oracle Statistics

The dbms_stats Oracle-supplied package makes it easy to back up and restore statistics.

Create the table where statistics will be captured. dbms_stats.create_stat_table
SQL> exec dbms_stats.create_stat_table('SCHEMAOWNER', 'STATTAB')
PL/SQL procedure successfully completed.


dbms_stats.export_schema_stats
This requires a table name where the statistics will be stored. This table can be exported (like any other table) and imported into a development database.

dbms_stats.import_schema_stats


Example of capturing and transferring statistics:
SQL> exec dbms_stats.export_schema_stats('SCHEMAOWNER','STATTAB')
PL/SQL procedure successfully completed.


$ exp file=stattab.dmp tables=SCHEMAOWNER.stattab compress=n

About to export specified tables via Conventional Path...
Current user changed to SCHEMAOWNER
. . exporting table STATTAB
8701 rows exported
Export terminated successfully without warnings.


>>> Copy export file and change ORACLE_SID as appropriate.


$ sqlplus SCHEMAOWNER@qa
SQL> delete from stattab;


$ imp file=stattab.dmp fromuser=SCHEMAOWNER touser=SCHEMAOWNER ignore=y

Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SCHEMAOWNER's objects into SCHEMAOWNER
. . importing table "STATTAB"
8701 rows imported
Import terminated successfully without warnings.


$ sqlplus SCHEMAOWNER@qa
SQL> exec dbms_stats.import_schema_stats('SCHEMAOWNER','STATTAB')
PL/SQL procedure successfully completed.

Friday, November 03, 2006

Vista Upgrade Advisor

This tool can help determine if legacy equipment will support Windows Vista.

I ran the tools on a laptop purchased this year, and the tool says I would need a TV tuner card to run the home premium version of Vista.

"TV Tuner card
Make sure you have an approved TV tuner card No compatible TV tuner was detected. Upgrade Advisor did not detect a TV tuner card in your current system. To watch TV on your computer, you need an approved USB-based TV tuner or TV tuner card. Contact your computer manufacturer for more information.
"

Saturday, October 28, 2006

HP-UX Networking Utilities

$ uname -a
HP-UX myhost B.11.11 U 9000/800 2155931687 unlimited-user license


$ netstat -i
Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll
lo0 4136 loopback localhost 2531982 0 2531982 0 0
lan902 1500 10.1.22.0 qhus019nas 268978790 0 220114906 0 0
lan901 1500 10.1.18.0 qhus019afe 302001865 0 72493411 18 0
lan900 1500 10.1.20.0 qhus019adm 136182939 0 192680026 0 0


$ lanscan
Hardware Station Crd Hdw Net-Interface NM MAC HP-DLPI DLPI
Path Address In# State NamePPA ID Type Support Mjr#
0/3/1/0/5/0 0x001185C88445 3 UP lan3 snap3 3 ETHER Yes 119
0/3/1/0/6/0 0x001185C88446 4 UP lan4 snap4 4 ETHER Yes 119
0/3/1/0/7/0 0x001185C88447 5 UP lan5 snap5 5 ETHER Yes 119
0/4/1/0/4/0 0x001185C8A3D0 6 UP lan6 snap6 6 ETHER Yes 119
0/4/1/0/5/0 0x001185C8A3D1 7 UP lan7 snap7 7 ETHER Yes 119
0/4/1/0/6/0 0x001185C8A3D2 8 UP lan8 snap8 8 ETHER Yes 119
0/4/1/0/7/0 0x001185C8A3D3 9 UP lan9 snap9 9 ETHER Yes 119
0/2/1/1 0x0012799E7FA3 2 UP lan2 snap2 11 ETHER Yes 119
LinkAgg0 0x0013217CD2E5 900 UP lan900 snap900 15 ETHER Yes 119
LinkAgg1 0x001185C88444 901 UP lan901 snap901 16 ETHER Yes 119
LinkAgg2 0x0012799E7FA2 902 UP lan902 snap902 17 ETHER Yes 119
LinkAgg3 0x000000000000 903 DOWN lan903 snap903 18 ETHER Yes 119
LinkAgg4 0x000000000000 904 DOWN lan904 snap904 19 ETHER Yes 119


$ lanadmin -x 0
Speed = 1000 Full-Duplex.
Autonegotiation = On.
$ lanadmin -x 1
Current Config = 100 Half-Duplex AUTONEG
$ lanadmin -x 6
Current Config = 100 Full-Duplex MANUAL


$ ioscan -fnkClan
Class I H/W Path Driver S/W State H/W Type Description
========================================================================
lan 0 0/1/2/0 igelan CLAIMED INTERFACE HP PCI 1000Base-T Core
lan 20 0/2/1/0 iether CLAIMED INTERFACE HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter
lan 2 0/2/1/1 iether CLAIMED INTERFACE HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter
lan 1 0/3/1/0/4/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan1 /dev/ether1 /dev/lan1
lan 3 0/3/1/0/5/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan3 /dev/ether3 /dev/lan3
lan 4 0/3/1/0/6/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan4 /dev/ether4 /dev/lan4
lan 5 0/3/1/0/7/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan5 /dev/ether5 /dev/lan5
lan 6 0/4/1/0/4/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan6 /dev/ether6 /dev/lan6
lan 7 0/4/1/0/5/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan7 /dev/ether7 /dev/lan7
lan 8 0/4/1/0/6/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan8 /dev/ether8 /dev/lan8
lan 9 0/4/1/0/7/0 btlan CLAIMED INTERFACE HP A5506B PCI 10/100Base-TX 4 Port
/dev/diag/lan9 /dev/ether9 /dev/lan9
lan 10 0/6/1/0 iether CLAIMED INTERFACE HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter
lan 11 0/6/1/1 iether CLAIMED INTERFACE HP A7012-60001 PCI/PCI-X 1000Base-T Dual-port Adapter

Saturday, October 21, 2006

SFTP Scripting

SFTP has the expanded features of ftp, with the security of scp.
The typical ssh keygen and authorized_keys file are needed to set up a remote login without a password prompt. Configuring and testing login without password can be done with ssh or scp or sftp.

When sftp is able to log in without a password prompt, it is then time to create a batch file. The batch file contains sftp commands. In the batch file, a leading hyphen "-" instructs sftp to ignore an error.
$ cat sftp.batch
-rm /u001/testfile.Z
put /u001/testfile.Z
ls -l /u001/testfile.Z


The command is:
/bin/sftp -bsftp.batch ftpuser@ftphost

Monday, September 18, 2006

HP-UX top

It can be useful to read the changes document that accompanies each new software version. On HP-UX, a flag was added to the top command to not display individual CPU utilization metrics. This can be useful for allowing more process lines to be displayed on machines that have many CPUs.

From the HP example:
Once a 2 processor system, executing top displays individual CPU statistics:

System: 2wide Tue Apr 20 16:15:08 1
999
Load averages: 0.50, 0.51, 0.85
101 processes: 96 sleeping, 4 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 1.00 91.1% 0.0% 0.8% 8.1% 0.0% 0.0% 0.0% 0.0%
1 0.00 0.0% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.50 45.5% 0.0% 0.4% 54.1% 0.0% 0.0% 0.0% 0.0%

Memory: 125556K (101488K) real, 140160K (121952K) virtual, 14404K free Pag
e# 1/3
.
.
.


Once the same 2 processor system, executing top -h displays the average CPU statistics:

System: 2wide Tue Apr 20 16:15:59 1
999
Load averages: 0.50, 0.51, 0.84
101 processes: 96 sleeping, 4 running, 1 zombie
Cpu states: (avg)
LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0.50 49.6% 0.0% 1.2% 49.2% 0.0% 0.0% 0.0% 0.0%

Memory: 131488K (107388K) real, 146016K (127172K) virtual, 8460K free Page
# 1/3
.
.
.

Sunday, September 17, 2006

Oracle OCI Error

Recently a PC user was able to use most functionality of an application. One part of the application would consistently encounter ORA-01036: illegal variable name/number. In this case, the issue was an incorrect version of Oracle networking on the PC. The issue was resolved with removal of the more recent Oracle networking version, and installation of the correct version.

Thursday, September 07, 2006

Generate Oracle ReGrants "WITH GRANT OPTION"

Oracle user "tableowner" owns tables.
User tableowner grants SELECT to user "reportowner".
Report owner creates view "V" and grants SELECT on V to role "reportrole".
Role "reportrole" is granted to user "enduser".

The reportowner is able to create and select from the views. Enduser receives permission errors when selecting from reportowner's views. To resolve this issue, grant select WITH GRANT OPTION to the selectrole.

In this case the SELECT grants were already in place, and they needed be upgraded to include "WITH GRANT OPTION". This SQL will generate a SQL script that can be run to apply the "WITH GRANT OPTION".

$ cat gen_grantSelect.sql
set lines 90
set pages 0
set feedback off

spool grantSelect.sql
prompt spool grantSelect

select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || 'REPORTOWNER with grant option;'
from dba_tab_privs
where grantor='TABLEOWNER' and grantee = 'REPORTOWNER'
order by 1
/
spool off

Tuesday, September 05, 2006

Oracle Archive Log Mode

Follow these steps to take an Oracle database out of archive log mode.

Log in to sqlplus as sysdba.

-- shutdown the instance.
shutdown
startup mount exclusive
alter database noarchivelog
alter database open
-- check alert.log for archiving messages.



Follow these steps to put an Oracle database into archive log mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5554802160 bytes
Fixed Size 744944 bytes
Variable Size 2164260864 bytes
Database Buffers 3388997632 bytes
Redo Buffers 798720 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean TRUE
SQL> alter system switch logfile;

System altered.


From alert.log:
Tue Aug 8 22:09:10 2006
Thread 1 advanced to log sequence 4224
Current log# 4 seq# 4224 mem# 0: /u111/oradata/KANQA/redo04a.log
Current log# 4 seq# 4224 mem# 1: /u110/oradata/KANQA/redo04b.log
Tue Aug 8 22:09:10 2006
ARC0: Evaluating archive log 6 thread 1 sequence 4223
ARC0: Beginning to archive log 6 thread 1 sequence 4223
Creating archive destination LOG_ARCHIVE_DEST_2: '/u116/oraarchive/KANQA/arch4223.arc'
ARC0: Completed archiving log 6 thread 1 sequence 4223


Verify the archived log file has been created.
$ ls -l /u116/oraarchive/KANQA
total 2656
-rw-rw---- 1 oracle dba 1352704 Aug 8 22:09 arch4223.arc

Saturday, August 26, 2006

Thursday, August 03, 2006

Oracle SQLNet Client Tracing

Enabling sql*net client tracing can be useful for tracking down issues such as disconnects. Server-side tracing (alter session set sql_trace=true) is usually easier to interpret and includes statement execution time. If there are connection problems, then client-side tracing is appropriate.

How to implement on unix:
$ cd $ORACLE_HOME/network/admin
$ cp sqlnet.ora .sqlnet.ora.previous
$ echo "trace_level_client=16" >> sqlnet.ora
$ echo "trace_unique_client=yes >> sqlnet.ora
$ echo "trace_file_client=sqlnet.trc >> sqlnet.ora
$ echo "trace_directory_client=/u001/oracle >> sqlnet.ora

Ensure the trace file directory is world writable.
The trace files will be named sqlnet_PID.trc, where PID is the unix process ID.
Trace level 16 will create a large log file.

Tuesday, July 25, 2006

Oracle Processes And Sessions

The processes configuration parameter sets the upper limit on the number of processes (including background processes). Each user session can use one or more processes.

Determine the current settings with the following queries.

select name, value from v$parameter where name in ('processes', 'sessions');

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
processes
300

sessions
600



In this query, column SESSIONS_HIGHWATER is useful for determining if the running instance is close to the limit.

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 161 246 0

Oracle Utility maxmem

maxmem is a utility that ships with the unix version of the Oracle database. It is useful for showing available free memory on the unix machine.

maxmem is in $ORACLE_HOME/bin, and takes no parameters.

$ uname -a
HP-UX host21 B.11.11 U 9000/800 1288319306 unlimited-user license

$ swapinfo -t
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 35651584 0 35651584 0% 0 - 1 /dev/vg21_SWAP/lvol1
reserve - 20312372 -20312372
memory 27341052 3836680 23504372 14%
total 62992636 24149052 38843584 38% - 0 -

$ maxmem
Memory starts at: 9223372041149874176 (8000000100020000)
Memory ends at: 9223372047592194047 (800000027fffffff)
Memory available: 6442319871 (17ffdffff)

Oracle SQL via dbms_job

For long-running SQL, it can be useful to use dbms_job.submit so that the SQL will be detached from the client terminal. A SELECT statement would need to write results out to another table. Errors will be recorded to the database alert.log.



Create a test, in file named file.sql:
set lines 100
create table test (cola int);
select * from user_jobs;

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'insert into test values (1);');
commit;
END;
/

print jobno
select job,what from user_jobs;

exec dbms_lock.sleep(10)
select * from test;

select job,what from user_jobs;



Submit the test:
sqlplus -s user@SID @file.sql


Table created.


no rows selected


PL/SQL procedure successfully completed.


JOBNO
----------
208


JOB
----------
WHAT
----------------------------------------------------------------------------------------------------
208
insert into test values (1);



PL/SQL procedure successfully completed.


COLA
----------
1


no rows selected

Thursday, July 06, 2006

HP-UX Memory Page Size Tuning With chatr

Useful links.

HP-UX Kernel Tuning and Performance Guide, from 2000. Mostly versions 9 and 10, with a section on 11.0.

chatr information.
chatr +pd=256M +pi=16M progname.x
This will increase the data page size to 256MB and the instruction page size to 16MB. This will result in fewer virtual memory pages, and hence requires fewer page translation entries in the tlb.


chatr information.
To improve Translation Lookaside Buffer (TLB) hit rates in an
application running on a PA 8000-based system, use the
following linker or chatr virtual memory page setting options:
+pd size -- requests a specified data page size of 4K bytes,
16K, 64K, 256K, 1M, 4M, 16M, 64M, 256M, or L. Use L to
specify the largest page size available. The actual page
size may vary if the requested size can not be fulfilled.
+pi size -- requests a specified instruction page size. (See
+pd size for size values.). The default data and instruction
page size is 4K bytes on PA-RISC systems.

The PA-RISC 2.0 architecture supports multiple page sizes, from
4K bytes to 64M bytes, in multiples of four. This enables large
contiguous regions to be mapped into a single TLB entry. For
example, if a contiguous 4MB of memory is actively used, 1000
TLB entries are created if the page size is 4K bytes, but only 64
TLB entries are created if the page size is 64K bytes.
Examples:
To set the virtual memory page size by using the linker:
ld +pd 64K +pi 16K /opt/langtools/lib/crt0.o myprog.o -lc
To set the page size by using chatr:
chatr +pd 64K +pi 16K a.out
See also “Performance Optimized Page Sizing”:
http://www.unixsolutions.hp.com/products/hpux/pop.html

Monday, July 03, 2006

Personal Content Aggregation Sites

I recall something like this in 1999 that was specific to Internet Explorer.

Netvibes
Pageflakes
Confluence Commons

Thursday, June 29, 2006

Microsoft Genuine Advantage Uninstall

How to disable or uninstall the pilot version of Microsoft Windows Genuine Advantage Notifications

This Microsoft update does not have an easy uninstall. Removal will require modifying the registry.


Important This article contains information about how to modify the registry. Make sure to back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 (http://support.microsoft.com/kb/256986/) Description of the Microsoft Windows registry

SUMMARY
This article applies to the version of Microsoft Windows Genuine Advantage (WGA) Notifications that is distributed during the pilot program. For example, this version is included in the pre-release version that accompanies the Microsoft Software License Terms. To safely and easily uninstall the pilot version, you must install the general release version of WGA Notifications. If you do not install this version, you can follow the steps in this article to disable or uninstall the pilot version.

Sunday, June 25, 2006

Oracle Import Through Uncompressing Pipe

If there is not enough disk space to uncompress the dmp file, import can read from a pipe. This is similar to export into a compressing pipe.

Put this into a sh script file, and set your environment with the appropriate Oracle variables.
# The COMPRESSED dump file name.
DMPFILE=/u01/data/exportfile.dmp.Z

DT=`date +%Y%m%d%H`
PIPENAME=/tmp/pipe.$$

$(mknod $PIPENAME p)
/usr/bin/uncompress < $DMPFILE > $PIPENAME &

imp userid=user/pass file=$PIPENAME log=/tmp/imp_$DT.log fromuser=fromschemaowner touser=toschemaowner

Get IP Address & Browser

There are many sites that will get your IP address. This Dlink site is useful and without advertising.

Friday, June 23, 2006

Oracle Statistics

Implementation of automated statistics collection for application schemas in Oracle 9iR2.

dbms_stats is a supplied package, and is the recommended method of gathering statistics. dbms_utility and analyze are no longer recommended for production systems.

Table monitoring is a feature that counts the approximate number of changes to a table. dbms_stats with "gather stale" will analyze tables that have had more than 10% of their rows changed.

Overview
Determine application schemas to analyze.
Begin table monitoring for the schemas.
Schedule job to analyze stale for the schemas.

Details
Determine application schemas to analyze.
SQL> select username from dba_users order by 1;


Begin table monitoring for the schemas.
SQL> exec dbms_stats.alter_schema_tab_monitoring('SCHEMA_NAME', TRUE)
PL/SQL procedure successfully completed.


Schedule dbms_stats without or with histograms.
exec dbms_stats.gather_schema_stats( -
ownname => 'SCHEMA_NAME', -
options => 'GATHER AUTO' -
);

exec dbms_stats.gather_schema_stats( -
ownname => 'SCHEMA_NAME', -
options => 'GATHER AUTO', -
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254' -
);

Sunday, June 18, 2006

Oracle sqlplus Hide Password

There are several ways to keep the Oracle account password off of the unix command line and away from prying eyes. Connecting as sysdba or using an operating system authenticated account avoids a password altogether.
If you would prefer to hard code a password, this method may be used. It can easily be placed in a script.

$ORACLE_HOME/bin/sqlplus /nolog << EOF
conn username/password@SID

select sysdate
from dual
/
EOF


Some operating systems have the "w" command.
$ w
8:55pm up 9 days, 15:59, 5 users, load average: 0.02, 0.04, 0.89
User tty login@ idle JCPU PCPU what
user1 pts/0 6:58pm 1:57 -ksh
oracle pts/2 1:37pm 25 -ksh
user2 pts/5 7:06pm 54 19 19 sqlplus -s username/pass@THESID


Using "ps" to view the process list, notice how user1 does not expose the password.
$ ps -ef|grep sqlplus
user2 24595 24458 4 19:15:32 pts/5 0:20 sqlplus -s username/pass@THESID
oracle 2758 25017 0 20:55:17 pts/3 0:00 grep sqlplus
user1 28815 28641 0 20:00:02 ? 0:00 sqlplus -s /nolog

Tuesday, June 13, 2006

Oracle Tablespace Transport

Oracle's transportable tablespace feature is can save a lot of time when copying a user's data. All objects will need to be in one tablespace, so it may not work for all environments. When a schema's objects are in one tablespace, this is a quick way to copy a schema and data.


Verify all objects are in one tablespace. Then SELECT to see any problems.
SQL> exec sys.dbms_tts.transport_set_check('TABLE_SPACE_NAME', true)

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected


Determine names of data files.
SQL> select file_name, file_id, tablespace_name from dba_data_files where tablespace_name='TABLE_SPACE_NAME'

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oradata/SID/ts01.dbf
6 TABLE_SPACE_NAME

/u01/oradata/SID/ts02.dbf
43 TABLE_SPACE_NAME


Set the tablespace to read only.
SQL> alter tablespace TABLE_SPACE_NAME read only;

Tablespace altered.


To export transportable, user must log on as sysdba: as sysdba
File expdat.dmp will be created. The file will be small because it will contain just metadata.
$ exp transport_tablespace=y tablespaces=TABLE_SPACE_NAME


Copy data files to new location, then and set ORACLE_SID.
$ cp ...
$ export ORACLE_SID=NEWSID


To import transportable, the user must log on as sysdba: as sysdba
$ imp file=expdat.dmp transport_tablespace=y "datafiles=(/u02/oradata/NEWSID/ts01.dbf,/u02/oradata/NEWSID/ts02.dbf)" tts_owner=SCHEMA_OWNER_NAME

Import: Release 9.2.0.5.0 - Production on Mon Jun 12 13:31:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: username as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCHEMA_OWNER_NAME's objects into SCHEMA_OWNER_NAME
. . importing table "TABLE1"
. . importing table "TABLE2"
Import terminated successfully without warnings.


The imported tablespace is read only and the objects have been imported.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'TABLE_SPACE_NAME';

TABLESPACE_NAME STATUS
------------------------------ ---------
TABLE_SPACE_NAME READ ONLY


If desired, set the tablespace to read write.
SQL> alter tablespace TABLE_SPACE_NAME read write;

Tablespace altered.

Monday, June 12, 2006

wall

Before there was instant messenger, there was wall and talk. The unix command wall sends a broadcast message to terminals. The message may be in a file, or the message may be typed interactively.

For interactive use type wall, then the message, then press ctrl-d.


Example on HP-UX:
testuser@host $ wall
Testing 1 2 3 ...
PRESS CTRL-d HERE

Broadcast Message from testuser (pts/0) Mon Jun 12 15:41:54...
Testing 1 2 3 ...

Wednesday, June 07, 2006

Quick Guide To Sudo

The very quick quide to sudo. This guide is for end users.

Sudo is configured by root to allow a user to run a command as root. If a user is granted all sudo privileges, then the user can do anything. Sudo eliminates password hassles, because the user will only need to know their own passoword.
To become root, a user would type:
$ sudo su - root
The user would be prompted for their own password.
A user can list their sudo privileges with:
$ sudo -l

Users will often be granted sudo for specific commands - such as starting and stopping a service or becoming another user. In this case, the user could (for example) start or stop a web server without needing the root password.

Application software may be installed as user appowner with group appgroup. The application files will typically be group/world read so there is limited ability to accidentally delete the files. There will typically be other users with group appgroup, so the users can do most tasks with their own account. There will be times , for example application patching, when a user will need to become the application owner. The application owner password could be shared. Alternatively, users could be granted sudo permission to become the application owner.

Sudo typically is configured to log sudo access. Sometimes this is used as a security/auditing feature. When configured to write to syslog, an entry will look like:
Jun 7 10:05:22 host001 sudo: usera : TTY=pts/1 ; PWD=/home/usera ; USER=root ; COMMAND=/usr/bin/su - oracle
Be careful when relying on these logs, as the security depends on the correct functioning of the commands that are run under sudo. For example, allowing "sudo vi /etc/hosts" will allow the user to spawn a shell as root and this will not be logged.

Alternatives to sudo are using setuid and setgid (which have significant gotchas). Solaris has Role Based Access Control, which provides more granular privileges.

Saturday, May 27, 2006

Search Engine Optimization

SE-Tools has some tools for search engine optimization, including checking if your site is listed in the search engines.

Wednesday, May 24, 2006

Commands To Monitor NFS

Commands for monitoring NFS performance.

$ netstat -s -p tcp

$ nfsstat -c

$ nfsstat -m
/u001 from nas002:/client9_u001 (Addr 10.1.22.201)
Flags: vers=3,proto=tcp,auth=unix,hard,intr,link,symlink,devs,rsize=32768,wsize=32768,retrans=5
All: srtt= 0 ( 0ms), dev= 0 ( 0ms), cur= 0 ( 0ms)

/apps from nas002:/client9_apps (Addr 10.1.22.201)
Flags: vers=3,proto=tcp,auth=unix,hard,intr,link,symlink,devs,rsize=32768,wsize=32768,retrans=5
All: srtt= 0 ( 0ms), dev= 0 ( 0ms), cur= 0 ( 0ms)

$ rpcinfo
program version netid address service owner
100000 4 ticots client9.rpc rpcbind superuser
100000 3 ticots client9.rpc rpcbind superuser
100000 4 ticotsord client9.rpc rpcbind superuser
100000 3 ticotsord client9.rpc rpcbind superuser
100000 4 ticlts client9.rpc rpcbind superuser
100000 3 ticlts client9.rpc rpcbind superuser
100000 4 tcp 0.0.0.0.0.111 rpcbind superuser
100000 3 tcp 0.0.0.0.0.111 rpcbind superuser
100000 2 tcp 0.0.0.0.0.111 rpcbind superuser
100000 4 udp 0.0.0.0.0.111 rpcbind superuser
100000 3 udp 0.0.0.0.0.111 rpcbind superuser
100000 2 udp 0.0.0.0.0.111 rpcbind superuser
100024 1 tcp 0.0.0.0.192.0 status superuser
100024 1 udp 0.0.0.0.192.1 status superuser
100021 1 tcp 0.0.0.0.192.1 nlockmgr superuser
100021 1 udp 0.0.0.0.192.2 nlockmgr superuser
100021 3 tcp 0.0.0.0.192.2 nlockmgr superuser
100021 3 udp 0.0.0.0.192.3 nlockmgr superuser
100021 4 tcp 0.0.0.0.192.3 nlockmgr superuser
100021 4 udp 0.0.0.0.192.4 nlockmgr superuser
100020 1 udp 0.0.0.0.15.205 llockmgr superuser
100020 1 tcp 0.0.0.0.15.205 llockmgr superuser
100021 2 tcp 0.0.0.0.192.4 nlockmgr superuser
805306352 1 tcp 0.0.0.0.2.151 - superuser



Not NFS specific:
$ netstat -i
Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll
lo0 4136 loopback localhost 74916346 0 74916352 0 0
lan902 1500 10.1.22.0 client9nas 625727357 0 628752686 0 0
lan901 1500 10.1.18.0 client9afe 835324548 0 538488630 0 0
lan900 1500 10.1.20.0 client9adm 12442786 0 17658172 0 0

Sunday, May 21, 2006

Extract Index DDL

When restoring tables with export/import, it is common to disable any constraints, drop any non-primary key index, and disable any trigger. After the import simply enable constraints, enable triggers, and rebuild any index.

A quick way to get the index DDL is by using the export dump file. Run the dump file through import with the "indexfile" option.
imp file=export.dmp indexfile=index.sql fromuser=theSchemaOwner

File "index.sql" will be created. The tables will be in the file and commented out.

I learned this from Tom's web site.

Tuesday, May 16, 2006

Quickly Generate Oracle Object Grants

This quickly generates an Oracle SQL script that can be called to grant privileges to a user or role. Extend for other object types.

select 'grant execute on ' || object_name || ' to &&userOrRole;' || ' -- ' || object_type
from dba_objects where owner='&&OWNER' and object_type in ('PACKAGE', 'PROCEDURE')
union all
select 'grant select, insert, update, delete on ' || object_name || ' to &&userOrRole;' || ' -- ' ||
object_type
from dba_objects where owner='&&OWNER' and object_type in ('TABLE', 'VIEW')
/

Sunday, May 14, 2006

Unix Remote Login Without Password

This has been covered in many places, so this will be a short version. This is not using the insecure r?? commands with .rhosts.

Assume both machines have the same or similar ssh installation.

"hosthere" is where you are logged on. "hostfar" is where you want to log on.

Use an empty passphrase and default key location.
hosthere $ ssh-keygen -t dsa

Place "pub" file on hostfar in ~/.ssh as "authorized_keys2".
hosthere $ scp id_dsa.pub hostfar:.ssh/authorized_keys2

Now ssh and scp to "hostfar" should not prompt for a password.

Troubleshooting includes checking permissions of .ssh directory and files.

Tuesday, May 09, 2006

Open Source Server Monitoring

munin and monit

cacti

http://www.orcaware.com/orca/Orca creates plots from flat files.

Nagios

Oracle Profile Limit Not Working

It can be useful to set a high CPU usage limit to stop runaway queries. "High" is application-specific, so set the limit as appropriate.

When the limit is reached, the session will be killed with the message, "ORA-02393: exceeded call limit on CPU usage".

If the limit does not work, verify the resource_limit parameter is set to true.


prompt -- Set pfile for: resource_limit=true
alter system set resource_limit=true scope=both;

drop profile profile_example
/

CREATE PROFILE profile_example
LIMIT CPU_PER_SESSION UNLIMITED CPU_PER_CALL 120000
CONNECT_TIME UNLIMITED IDLE_TIME UNLIMITED SESSIONS_PER_USER
UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED PASSWORD_VERIFY_FUNCTION NULL
/

alter user limt_me profile profile_example
/

Tuesday, April 25, 2006

Online Office Suite

ThinkFree offers 1GB of storage for their online word processor, spreadsheet, and presentation tool.

There are quick and full editors. Any formatting will need the full editor, which runs in a jvm. The full editor allows saving to PDF.

Wednesday, April 12, 2006

What's On My PC

February 2006 we received a zv6000 laptop from HP. 15.4" monitor, AMD x64, lots of built in hardware, and running XP Home. This is the software I have installed and updated.

First go to MS Update and get patched.

Get the CPU information with WCPUID. Depending on power settings, this AMD will throttle down and show significantly fewer MHz.
A couple alternative browsers: Opera and Firefox.
Winamp music player.
Real player. Deny the TkBellExe browser help object.
OpenOffice office productivity.
ClamWin virus scanner.
Protect from spyware.
X-Ray displays running process information to allow removal of removal of spyware. This is for more advanced users.
X-Cleaner free version does a reasonable job finding and cleaning malware.
Hosts file changes to reduce advertisements while browsing.
Site Advisor installs in Firefox or Internet Explorer and rates the safety of web sites.
Adobe Acrobat reader.

Power archiver works on TARs.
SFTP client
===== Some others. =====

Check the prices of gold and silver.

For those supporting remote machines, try Real VNC to forward a screen.
PuTTY is a telnet and ssh character-based logon tool.

Thursday, April 06, 2006

Uncommon Oracle Startup Errors

An installation running Oracle 9iR2 databases in production had non-typical messages in the alert.log. The databases had been installed on a fresh OS install, so there were no upgrade issues. The databases had been running for over a year.

During startup, non-default parameters are listed in the alert log. There were several messages about deprecated mts_ parameters. The installation DBA had taken favorite Oracle 8i settings and dropped them into Oracle 9i. The DBA had English as a second language, and did not investigate the meaning of deprecate. The alert.log message did not include an "ORA-99999", so the DBA ignored the message.
Simply setting the values to zero did not remove the messages - the parameters needed to be removed from the spfile. From alert.log:
Deprecated system parameters with specified values:
mts_servers
mts_max_servers
mts_max_dispatchers
End of deprecated system parameter listing


The next alert.log message was, "Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid severe performance degradation."
This message did not include an "ORA-99999", so this message was also ignored. The solution for this issue was to increase the operating system file descriptors. In the case of HP-UX 11.11, query the OS kernel to check the open file limits:
$ /usr/sbin/kmtune |grep files
maxfiles 4000 - 4000
maxfiles_lim 4000 Y 4000

Wednesday, April 05, 2006

Oracle EXP-00091, Exporting Questionable Statistics

When performing an Oracle export, you may receive error, "EXP-00091: Exporting questionable statistics."

The first thing to do is gather a little more information with "oerr" on the command line:
$ oerr exp 91
00091, 00000, "Exporting questionable statistics."
// *Cause: Export was able export statistics, but the statistics may not be
// usuable. The statistics are questionable because one or more of
// the following happened during export: a row error occurred, client
// character set or NCHARSET does not match with the server, a query
// clause was specified on export, only certain partitions or
// subpartitions were exported, or a fatal error occurred while
// processing a table.
// *Action: To export non-questionable statistics, change the client character
// set or NCHARSET to match the server, export with no query clause,
// export complete tables. If desired, import parameters can be
// supplied so that only non-questionable statistics will be imported,
// and all questionable statistics will be recalculated.

Then search Oracle's documentation:
The precalculated optimizer statistics are flagged as questionable at export
time if:
* There are row errors while exporting
* The client character set or NCHAR character set does not match the server character set or NCHAR character set
* A QUERY clause is specified
* Only certain partitions or subpartitions are exported

The NCHAR setting often causes this message, and the message will often be avoided by setting the NLS_LANG environment variable to match the database setting.

Questionable statistics are ususally a minor inconvenience, because after the import it is easy to re-analyze. Additionally, you may set "statistics=safe" on the import to skip importing questionable statistics.

Oracle Role Insufficient For User's Procedure To Compile

Roles are useful, though there are a few limitations. This is one limitation of Oracle roles.

Table owned by userA. UserB has "select any table" and creates SQL that sucessfully runs in sql*plus. When userB tries to put the SQL in a package, there is ORA-00942 compile error.
The solution is to directly grant the userA.table_name SELECT privilege to userB. It's not enough to have a role that allows the SELECT permission.

Example:
Create package header with no problems, then try to create package body:

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY xPKG:

LINE/COL ERROR
--------
-----------------------------------------------------------------
27/7 PL/SQL: SQL Statement ignored
72/29 PL/SQL: ORA-00942: table or view does not exist

-- In another session, directly grant SELECT on table_name to this user.

-- In this session, re-submit package body.

SQL> /
Package body created.

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.