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.

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.

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:

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

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".

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

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.

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."

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

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

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
.
.
.

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.

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

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

Peer To Peer VOIP Tracking

Interesting article describing tracking VOIP peer to peer calls.

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.

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

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

Personal Content Aggregation Sites

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

Netvibes
Pageflakes
Confluence Commons

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.

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.

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' -
);

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

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.

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 ...

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.

Search Engine Optimization

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

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

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.

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')
/

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.

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
/

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.

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.

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

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.

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

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

Sites WIth Great Ad Revenue

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

Article on ugly web sites.

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> \.

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.

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.

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.

DB2 Free Production License

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

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

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.

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.

Nokia 770 Internet Tablet

The Nokia 770 internet tablet is a small computer, not a phone. Coupled with a phone that supports Verizon's wireless broadband, this could a great combination for people who provide remote tech support and don't want to carry a laptop.

Google Web Publisher

Google has released a web publisher. The editor requires no knowledge of HTML, and publishes to a page name that contains the user's gmail user name.

Oracle Express

Oracle released an Express version of the 10g database. It is free to download and distribute, can run on one CPU, and can store 4GB of user data. It runs on MSWindows and Linux.

It would be great to see this run on Solaris 10, though it might be possible to run the Linux under Solaris...

Solaris x86 Hardware Compatiblity Check

Sun has a tool that will check a PC for compatibility with Solaris. The tool is delivered as an iso image to be burned onto a CD. The CD will boot and check the system.

Measuring Performance In Services by McKinsey

McKinsey Quarterly has an interesting article ,Measuring Performance In Services.

Here is a small part of the text. To do so, it is necessary to bear in mind a few essential principles of service measurement.

First, service companies need to compare themselves against their own performance rather than against poorly defined external measures. Using external benchmarks only compounds the difficulties that service companies face in getting comparable measurements from different parts of the organization.

Service companies must look deeper than their financial costs in order to discover and monitor the root causes of those expenses. This point may seem self-evident, yet many companies fail to understand these causes fully.

Finally, service companies must set up broad cost-measurement systems to report and compare all expenses across the functional silos common to service delivery organizations. The goal is to improve the service companies' grasp of the cross-functional trade-offs that must be made to rein in total costs.

None of these principles is easy to implement.

... by developing internal trees for each service line can a company begin to understand its true cost drivers. A tree allows a manager to compare the performance of different accounts against similar metrics and also to calculate which improvements will have the most impact on the top-level figure.

Mobile Data Extenders

A home office that is on the fringe of reliable and affordable communications could benefit from this gear.

The cost of cellular repeater is going down and now almost affordable for consumers. These have been available from other vendors, though expensive.

EV-DO router. There are others on the market.

This to-be-released VOIP handset from Netgear shows the VOIP industry is maturing. It is pre-loaded with Skype and may be easy enough to use by non-technical people.

Quick Flickr Front End With Ruby On Rails

This video shows how a custom front end to Flickr was created in a few minutes.

Useful (and free) Web Apps

Back Pack is an event organizer web page for a to-do list, photos, text. Free for personal use. Pages may be shared with some users or made public.
To Do List
White Board

AJAX and Google's Suggest

Not only is Google's Suggest an example of using AJAX, it is also a neat way to do a one-word Google search. Start typing the first letters of a word and it will suggest matches. The more you type, the more refined will become the suggested matches.

WiFi Peering

Share your WiFi and get free WiFi while travelling with FON. Currently limited to Linksys WRT54G/GS/GL versions 1x to 4x routers.
More info.

Oracle 9i DBA Fundamentals, Exam 1Z0-031

The Oracle 9i DBA Fundamentals Exam is the second of two required tests to achieve Oracle Certified Associate status.

These notes are from Exam Cram Oracle 9i Fundamentals 1 by April Wells.

Tablespace contains segments which contain extents which contain data blocks which contain OS blocks. Extents are specific to a data file. Segments can span data files.

Oracle Universal Installer can be run non-interactively using a template so that installs can be completely scripted. ./runInstaller -responsefile filename -silent

The password file allows access to administrative roles sysdba / sysoper to an OS user without divulging the adminsitrative passwords.
Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.

Oracle Enterprise Manager can be used to control a fleet of servers using Intelligent Agents and the Oracle Management Server schema. Each managed node requires one Intelligent Agent which communicates with OMS.

Oracle Flexible Architecture would have:
ORACLE_BASE the topmost directory,
ORACLE_HOME set to ORACLE_BASE/product/release/
Add ORACLE_HOME/bin to the PATH.
Set ORA_NLS33 to ORACLE_HOME/nls/data if using character set other than US7ASCII.
Add ORACLE_HOME/lib to LD_LIBRARY_PATH.

Startup: nomount mount open restrict recover force
If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.
STARTUP NOMOUNT can be used to create a new controlfile.

Oracle Managed File parameters: DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n
When using OMF, alter database add logfile; will create logfile with size of 100MB.

Startup nomount then create database.
CREATE DATABASE runs sql.bsq to create data dictionary base tables.
Install data dictionary views with ORACLE_HOME/rdbms/admin/catalog.sql.
Install PLSQL with catproc.sql.

To convert ORACLE_HOME/dbs/initSID.ora to spfile: create spfile from pfile;

alter system enable restricted session; Leaves sessions connected but new sessions can only be established by DBA and those with RESTRICTED SESSION privilege.

If not specified with startup pfile=filename, then starts with ORACLE_HOME/dbs/spfileSID.ora or spfile.ora or initSID.ora.

alter session set sql_trace=true
dbms_system.set_sql_trace_in_session

Show data dictionary tables: SELECT * FROM DICTIONARY [DICT CATALOG CAT]
Show data dictionary columns: SELECT * FROM DICT_COLUMS

V$ are all dynamic performance views.

DBA_SEGMENTS and DBA_EXTENTS both have column BLOCKS.

Block header contains: block address, row directory, table directory, transaction slots.
The table directory is the portion of the data block contains information about the table having rows in this block.
Blocks have a header that consist of:
* Block type
1=undo segment header, 2=undo segment block, 5=data segment header, 7=temporary table, 11=data file header, 14=Unlimited rollback segment header, 15=Unlimited deferred rollback segment header, 16=Unlimited data segment header, 17=Unlimited data segment header with FL groups, 18=Extent map block
* Block format
Is it an oracle 7 or oracle 8 or 9 or 10 formatted block?
* Relative database address
* SCN
* SCN sequence number
* Check value
Is used for integrity checking at the block level if db_block_checksum is set.
* Tail
The tail is not at the beginning of a block but at its end: it is used as a block consistency check. That is, the value must be equal to SCN + block type + SCN sequence number.

AL32UTF8 varying width multi byte character set.
AL16UTF16 fixed width multi byte character set.
UTF-8 unicode standard.
UTF8 Oracle implementation of unicode.
Database character set may not be fixed width multi byte.
National character set may be fixed width multi byte.
When developing a packaged application, UTF-16 and nvarchar data types will provide most portability to any customer site.
NLS_LANGUAGE determines: NLS_DATE_LANGUAGE NLS_SORT.
Set ORA_NLS33 to $ORACLE_HOME/ocommon/nls/admin/data
Default sorting method is binary.
V$NLS_PARAMETERS

System Change Number is stored in all data files and control files and redo log files.
alter database open resetlogs; This creates a new incarnation of the database and affects recovery.
create controlfile...; Create control file from scratch with information you provide. You need to know names of all data files.
alter database backup controlfile to '/path/name';
alter database backup controlfile to trace;
V$CONTROLFILE

FAST_START_MTTR_TARGET sets upper bound on recovery time by causing more frequent check points.
alter system switch logfile;
alter database clear [unarchived] logfile group ;
V$LOG
V$LOGFILES
V$LOGHISTORY Archived logs.
V$LOGHIST Online logs.
alter system archive log all; Does what the archiver process does.

CREATE TABLESPACE BLOCKSIZE needs a configured subcache to use - DB_[2 4 8 16 32]K_CACHE_SIZE. Temporary tablespace can not use nonstandard block size.
Tablespaces that can never be offline: system, default temporary, undo with active segments.
ALTER TABLESPACE OFFLINE NORMAL;
TEMPORARY; might need recovery
IMMEDIATE; will need recovery
FOR RECOVER; allows tablespace point in time recovery
ONLINE;
READ ONLY; still will allow objects to be dropped
READ WRITE; undoes READ ONLY
Temporary tablespace tuning should set UNIFORM EXTENT SIZE to multiple of SORT_AREA_SIZE.
BACKUP CONTROLFILE does not record temporary tablespace temp files.
CREATE CONTROLFILE does not allow specifying temporary tablespace.
Set a default temporary tablespace: alter database default temporary tablespace ;
To drop a tablespace for sure: drop tablespace including contents and datafiles cascade constraints;

Read consistency is implemented through undo and system change numbers.

SET TRANSACTION READ ONLY; provides transaction level repeatable read.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; provides transaction level repeatable read during read/write.

Types of undo: system, non-system, deferred. Deferred occurs when tablespace is offline for recovery.

Init.ora settings for system managed undo: undo_management [auto, manual] undo_tablespace=.

How to change the undo tablespace: alter system set undo_tablespace=;
When one instance of RAC is looking for a previous SCN in undo, does it look in it's undo then ask all other instances if it is available in their undo?

alter system set undo retention=;
Monitor via v$undostat.

Index organized table overflow holds columns that don't fit or that are excluded (PCTTHRESHOLD INCLUDING).

CHAR(n) 1-2000 bytes, blank padded.
VARCHAR2(n) 0-4000 bytes.
DATE 7 bytes.
TIMESTAMP Fraction of second, up to 9 digits, default is 6.
BLOB When greater than 4000 bytes, a locator points to out of line storage.
VARRAY Stores a list, manimum size is set.
External table:
create or replace directory as '/dir';
grant read on directory to user1;
create table ... organization external;

dbms_redefinition.can_redef_table to verify if it can be online redefined.
Create interim table.
dbms_redefinition.start_redef_table
dbms_redefinition.finish_redef_table

alter table move tablespace ;

Bitmap index may never be unique.
alter index rebuild online; online allows continued DML on the table.
alter index coalesce; Merges leaf blocks.
analyze index validate structure; Information is stored in index_stats.
alter index monitoring usage; Information is stored in V$OBJECT_USAGE.

Primary key constraint may not contain nulls.
Unique constraint may contain nulls.

drop table cascade constraints;
drop tablespace including contents cascade constraints;

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2427.htm#1315815
$ORACLE_HOME/rdbms/admin/utlexcpt.sql creates EXCEPTIONS table.
alter table enable valid constraint exceptions into EXCEPTIONS;

PASSWORD_LOCK_TIME number of days account will be locked after FAILED_LOGIN_ATTEMPTS
.

PASSWORD_REUSE_TIME days before password may be reused. PASSWORD_REUSE_MAX number of new passwords before reuse of previous password. These are mutually exclusive - use one or the other.

PASSWORD_LIFE_TIME days to expire password.

Oracle-supplied password verify_function is enabled by running utlpwdmg.sql. It changes the default profile.

ALTER RESOURCE COST changes the weighted values.

Init.ora RESOURCE_LIMIT

DBMS_RESOURCE_MANAGER package use requires privilege
ADMINISTER_RESOURCE_MANAGER, which is in DBA role.
Resource plans are created in the pending area. The resource plan guides resources to consumer groups.
execute dbms_resource_manager.create_pending_area;
execute dbms_resource_manager.clear_pending_area;
Create consumer group. There may be more than one consumer group per plan. Oracle provides consumer groups default_consumer_group and other_groups.
execute dbms_resource_manager.create_consumer_group;
execute dbms_resource_manager.create_plan(plan_name);
Plan directive specifies how resource are allocated to consumer groups.
execute dbms_resource_manager.create_plan_directive; This must always include the OTHER_GROUPS catch-all.
Individual users needs privileges to set initial group and to switch groups. It is convenient to grant the privileges to PUBLIC.
execute dbms_resource_manager.set_initial_consumer_group;
execute dbms_resource_manager.grant_switch_consumer_group;
Activate the resource manager: alter system set resource_manager_plan=<>;
DBA_RSRC_PLANS lists all resource plans.
V$RSRC_PLAN lists currently active plans.
DBA_RSRC_PLAN_DIRECTIVES lists all resource plan directives.

To move the audit trail out of the system tablespace:
alter table aud$ move tablespace <>'
create index <> on aud$(sessionid, ses$tid) tablespace <>;

Users with role SYSOPER can
STARTUP RESTRICT.

Extended ROWID is ten bytes encoded in base 64. Restricted ROWID is six bytes encoded in base 16.