Repair Windows Master Boot Record

A corrupt MS Windows master boot record can be serious, though is often simple to correct.

Boot from the installation CD and start the recovery console.  Use the fixmbr command to repair the boot record.

Overheating Laptop

A laptop that would intermittently turn itself off has been diagnosed as overheating.  Even with multiple underside fans running, the laptop could not keep itself cool.  The small form factors don't tolerate dust buildup.

Note the internal temperature of 63C in this picture from SpeedFan.

Microsoft Store Rejects IE 6

The Microsoft Store doesn't support Internet Explorer 6.

Fast User Switching

Windows XP Fast User Switching allows multiple simultaneous logons.  For PCs typically used by just one person at a time, disabling fast user switching will free up about 1 MB of system resources.

• Start button
• Control Panel
• User Accounts
• Change the way users log on or off
• Use Fast User Switching
• Apply Changes

Show File Name Extensions

In Windows XP, show file name extensions in Explorer:

• Start Button
• Control Panel
• Folder Options
• Click the View tab
• Uncheck Hide extensions for known file types 

Install XP on Dell Inspiron 1520

After struggling with MS Windows Vista on a Dell Inspiron 1520 laptop, the decision was made to "upgrade" to MS Windows XP Professional.

The laptop has SATA drive, and the XP install media does not contain SATA drivers. While it is possible to "slipstream" SATA drivers into the XP install media, it is more convenient to change BIOS setting to emulate ATA. Doing so allows XP to install.

Upon installation of XP, the bluetooth device was working. Ethernet, wifi, and the camera were not working. Without network adapters, patches could not be downloaded.

This document from the Dell support website explains the order of drivers to install. Using the laptop service tag, 24 drivers were recommended. Downloading drivers one-at-a-time onto a USB flash drive was successful. The laptop recognized the flash drive, and drivers were installed in the order Dell recommends until installation of the Broadcom 440x network driver (R149798.exe) enabled networking. After that, the normal patching process was completed.

The SATA drivers were not correctly installing, so the drive needed to stay in ATA mode.

Using the Windows installer version of Ubuntu Linux, the installation was straight-forward, simple, and quick. It has it's own boot menu entry. Upon switching from ATA to ACHI (to use SATA), it boots and runs normally.
Compared to the MS Windows install, the Ubuntu install was unbelievably easy.

High CPU Usage From DNS Client

Some MS Windows installations run slowly for several minutes, particularly after start-up or resumption from power savings mode. After several minutes, CPU usage will drop and the system will run normally. This may be caused by local caching of DNS lookups, which can be disabled. There will typically be no detrimental effects.

In task manager, look for 100% CPU usage by svchost.exe. The user name is NETWORK SERVICE.

Start Button --> Run --> services.msc

Right-click on "DNS Client", and choose "Properties".
Change "Startup Type" to "Disabled".
Either restart the system, or press "Stop" button.

Recover Lost Windows System File

A virus scanner removed the MS Windows rundll32.exe file. The system still booted and would run, with many annoyances.

How to replace MS Windows \WINDOWS\system32\rundll32.exe

Error message identifying missing file:

File is missing:

Open a command prompt and run System File Checker.
Start Button --> Run --> cmd
At command prompt type: sfc /scannow

With the installation CD in the CD drive, expand the file from CD to the MS Windows installation.

File rundll32.exe has been restored, with a current time stamp.

While not necessary, set the file attributes to read only.

Google Encrypted Search

Google now supports encrypted search through the https protocol.

Those using Opera web browser can quickly change the search bar to use Google's encrypted search.
1) In the search bar (upper right hand corner), click the dropdown arrow
2) Toward the bottom of the list, click "Manage search engines..."
3) Highlight "Google" and press EDIT button

4) Press DETAILS button
5) Replace the address line with:

https://www.google.com/search?q=%s

6) Press OK
7) Press OK

On a related encryption note, mobile phone voice security has been improved to include end-to-end encryption when using Android phones.

Save Memory, Remove Java Quick Starter

jqs.exe is the Java Quick Starter (jqs.exe) task that runs in MS Windows. It uses 2MB of virtual memory.

For those who infrequently use java programs or who need the CPU cycles, jqs.exe can be disabled from the Control Panel --> Java --> Advanced and unselected Java Quick Starter.

Microsoft Security Essentials

While I have been satisfied with products such as Spybot and Clamwin, Microsoft Security Essentials is free and may be worth a look.

Microsoft Security Essentials provides real-time protection for your home PC that guards against viruses, spyware, and other malicious software.

Windows Upgrade Stopped By DEP

While applying Microsoft's patches to a MS Windows Vista installating, the installer was stopped by Data Execution Prevention.

A screenshot was able to capture most of the message.

Faster Vista Booting

Press the start button and in the "Start Search" box type: msconfig

The "System Configuration" utility will run.

Choose the "Boot" tab.
Check "No GUI Boot". This will disable the splash screen during startup.

Click the "Advanced options..." button.
Check "Number of processors", and choose the highest number. This will allow multi-core (multi-CPU) processing during startup.

XP Memory Usage

While refreshing a Windows XP desktop, I noticed what would now be considered low memory usage. Compared to Vista desktops easily surpassing 1GB of memory usage, XP was using 81M while running IE. After patching, the XP memory usage increased to 107M.

USMint.gov Passwords

The US Mint website inexplicably disallows passwords that end with a number.

Windows Media Center Traps The Cursor

Running Windows Media Center (MCE) on Vista Premium with two monitors. When maximizing Media Center, the cursor would get trapped in the MCE window which made it impossible to work on the other monitor. Apparently this bug has been present since MSWindows XP, so it is a design feature.

MCEMultiMon (unzip it - no install program) provides a few commands to send MCE to different monitors and allows MCE to be maximized without trapping the cursor.

On this PC, the right monitor is #1 and the left monitor is #2. To make the program work properly, editing the configuration file to offset the window was necessary. The changes are in bold:
[Version]
Current=0.2
[Screen 1]
Enabled=1
Width=1280
Height=1024
X Offset=0
Y Offset=0
[Screen 2]
Enabled=1
Width=1152
Height=864
X Offset=-1152
Y Offset=0
[Screen 3]
Enabled=0
Width=1280
Height=1024
X Offset=2560
Y Offset=0
[Screen 4]
Enabled=0
Width=1280
Height=1024
X Offset=3840
Y Offset=0
[Default Screen]
Current=2
[Startup]
Run with Windows=0

The next feature of MCE that would be useful to change is to make the application volume control work for the application, and not change the volume for everything. For example, muting MCE mutes everything and this is not desired.

Blocking Web Ads

Firefox has add-ons to block advertisements. While these are effective within the specific browser, modifying the hosts file in MS Windows will provide a more complete solution that works in all browsers for all users of the PC.

Updating the HOSTS file in MS Windows Vista is described here.

Burn ISO To CD

I found isorecorder to be a handy utility to burn ISO images on MS Windows. Simply right-click on an iso file and choose "Copy image to CD".

Comcast DNS "Helper" Opt-Out

Comcast has started a domain name "helper" service that inserts a page of ads when a domain name does not resolve successfully.

Use this opt-out page to disable this new "feature". The page is accessible inside the Comcast network.

Microsoft Malware Scan

Running MSWindows XP Home on a laptop, there was a blue screen of death after attempting to start up from hibernation.

The following alert was displayed:
Malware alert: Problem caused by spooldr.sys, which might be malware

The system suggested a full malware scan. Microsoft has a malware scan tool available for free download.




Microsoft's tool removes orphaned registry entries and includes a disk defragment utility. Auslogics has a disk defrag that has been available for years.


Spyware Blaster provides some defenses to the Internet Explorer browser, along with flash ad blocking and custom system restore points.

Encrypted Password Storage On MSWindows

PasswordSafe stores passwords. The application for MSWindows is available at SourceForge.net.

The application is more than an encrypted notepad, it has entry fields specifically for password info. The info can be stored on one machine (in MSWindows registry) or as a file that can be shared.

Improving Laptop Readability On MSWindows

Text readability on LCD displays can be improved with the following applications available at no charge from Microsoft at http://www.microsoft.com/windowsxp/downloads/powertoys/xppowertoys.mspx

Look for download links:
ClearType Tuner (improve text readability)
Tweak UI (modify many UI attributes)

iTunes Startup Sludge

iTunes on MS Windows installs startup tasks that may be removed.

Start --> Run --> regedit.exe

In regedit, search for qttask. Look for the qttask under the key ...\CurrentVersion\Run. This is where startup tasks are stored.

In regedit, right-click on iTunesHelper and choose Delete. Then right-click on "QuickTime Task" and choose Delete. Exit regedit.

HDTV Antenna

I had been using an older Radio Shack amplified antenna. With proper fiddling, many of the available over-the-air HD channels would be received. Most channel changes would require antenna fiddling.

I built the following antenna, and it works good. The antenna is not amplified (uses no electricity) and has not required fiddling to receive all of the channels. The antenna is leaning behind the TV, in a less-than-optimal position that is mostly hidden from sight.

Supplies:
13' #10 solid copper wire.
board > 2.5' long.
10 washers.
10 drywall screws or wood screws.
75-to-300 ohm Transformer  
RG-6 coaxial cable

Cut 8 pieces of wire, 14" long.
Cut 2 pieces of wire, 20" long.

Mark 2" from top of wood board, then mark 5 3/4" three times. Total of four marks.

Bend each wire piece into V, so ends are 2.5" to 3" apart. Remove insulation from areas where washers will connect.

Loosely screw V into board with washer.

Attach wires A and B as pictured, with insulation removed under washers. Wires A and B are electrically isolated -- they cross but don't electrically connect.

In this diagram, the Vs are not proportionally correct. When measuring, the spacing will be proportional.

 \     /   \     /          \     /   \     /
  \   /     \   /            \   /     \   /
   \ /       \ /              \ /       \ /
 --------------------------------------------
 |  o         O --wire A---X-- O         o |B
 |   \       /                  \       /  |O
T|     \                              /    |T
O|       \                         /       |T
P|    /     \                    /     \   |O
 |  O         o --wire B---X-- o         O |M
---------------------------------------------
   / \       / \              / \       / \
  /   \     /   \            /   \     /   \
 /     \   /     \          /     \   /     \

Attach balun to wires A and B, in middle of each wire, near the "x" points.

Connect short, unlooped RG-6 coaxial cable between antenna and HDTV converter/tv.






Coat Hanger HDTV Antenna: Better Than Store Bought! AMAZING!

Alternative design for very long range reception: http://www.digitalhome.ca/ota/superantenna/design.htm

Disable Windows XP Services

To conserve memory, some services unneeded on a standalone XP desktop (mainly used for web access and some word processing) by setting the services to Disabled:
Alerter
Computer Browser
Shell Hardware Detection
TCP/IP NetBIOS Helper
SSDP Discovery Service
Server

Set to Manual:
Distributed Link Tracking Client
Security Accounts Manager
Smart Card

How to change service settings:
Start, Run..., services.msc, highlight a service, right-click, Properties.

PC Anti-Virus

Clamwin has been a trusted anti-virus solution for years. Clamwin stays resident, has scheduled scans, and integrates with Windows Explorer.

I tried the avast! solution on an older XP system.
The product installs a couple processes that start automatically as services, using about 25M or RAM. The avast! full boot scan is a great way to find and remove trojans. A few problems were found (in the recycle bin and in an old system restore point).








Results of full virus scan.




Due to RAM constraints of the older PC, I uninstalled the product after a boot-time trojan scan and full virus scan.

Windows XP Media Sharing CPU Usage

Using Windows XP, particularly after logon, sluggish performance and high CPU usage by wmpnetwk.exe may be noticed. Media Sharing uses the service provided by wmpnetwk.exe.

If you don't need media sharing for Windows Media Player, then the service does not need to be started.
Press the START button, RUN, and type: services.msc

Near the end of the list, highlight Windows Media Player Network Sharing Service. The STATUS will likely by "Started", and the STARTUP TYPE will likely be "Automatic".
Right-click the entry and choose "Properties".

Set "Startup type" to "Manual".  Press the "Stop" button to immediately stop the service, or wait until your next logon.

Disabling wmpnetwk.exe will save about 23,200K of memory.

Windows Media Player Sync'ing Cameras

In Vista, Windows Media Player suddenly started trying to sync to a camera when that was plugged in to USB. The preferred action is to use the "Windows import pictures" feature.


Follow these steps to stop Windows Media Player from trying to synchronize to a camera:
+ Start button
+ Control Panel
+ Hardware and Sound
+ AutoPlay

+ Look under Devices for your device, and choose Import Pictures using Windows
+ Save




It may be necessary to log off/on for the import feature to activate.

Time Synchronize via NTP (router, webcam)

Home wireless router DI-524 would forget the date and time when it would restart (power off, setting changes).
Time settings are on the router page http://192.168.0.1/tools_time.html and automatic NTP (network time protocol) may be configured with 0.north-america.pool.ntp.org in North America.
Save the setting and restart the device.

Trendnet Wireless Network Camera TV-IP110W can sync to a time server on page http://192.168.0.xxx/admin/setup.cgi?page=datetime


Localized time server pools may be found here.

TVersity

TVersity is a UPnP media server. It supports format conversions and streaming to multiple device types. The TVersity software is running on a MSWindows Vista PC.

TVersity can take podcasts and video/audio streams and convert and distribute the streams to other devices. It can also distribute local music, video, and pictures.

TVersity has built-in support for youtube and many RSS feeds.

Instead of streaming Bloomberg or CNN on a PC, I instead stream this video and radio and podcasts to a D-Link media player. I enjoy watching video podcasts on the sofa, and children can watch Sesame Street podcasts and other children's content.

The D-Link DSM-320 didn't support wireless WEP/WAP. I also found that using my in-building wireless caused glitches with the media server. I pulled CAT5 and video streaming is now smooth with the hard-wired connection.
The D-Link device has special codes to enable telnet and a small unix shell.

Firefox Color Management

The Mozilla browser (used by Firefox) has included color management. The color management is disabled by default, and when enabled can provide improved color management and picture display.

In the web browser address bar, type: about:config
Search for: gfx.color_management_enabled
Change gfx.color_management_enabled from false to true then restart the web browser.

VMware License Management Blues

It seems that many Windows administrators have pushed the flexibility of VM, and allowed a trojan horse on their machines.

There is a date-based license management problem that will stop some VMs from starting today.

(Distributed SaaS) PC File Sync

I rarely promote websites, but this service is so useful I may be able to stop carrying a thumb drive. It works like NFS, any is simple enough for everybody.

GetDropBox syncs files in a PC folder to the web and to other people's PCs (those who are authorized to share). A small program runs on each PC, or files can be retrieved via a web browser.

This is a simple way to share/sync files in a folder to multiple PCs and is particularly useful for carrying around files to multiple PCs.


From http://www.getdropbox.com/ :

Changes to your files are instantly synced across your computers.

It's seamlessly integrated into your desktop.

All of your stuff is available via the web, so you can get it no matter where you are.
Share the love Invite friends and family to a shared folder for easy collaboration around files.

You can undelete files or restore old versions in a snap.

Inaccurate Gigabyte Definition

Seagate to repay customers over inaccurate gigabyte definition
Qualified hard drive buyers can choose cash or backup software

(Computerworld) -- Seagate Technology LLC has agreed to settle a lawsuit by offering customers who purchased a hard drive from the company during the last six years a cash refund or free backup and recovery software.
...

Oracle PLSQL Number Datatype Exploit

Researcher finds new way to hack Oracle database
'Lateral SQL injection' details released in paper

Security researcher David Litchfield has released technical details of a new type of attack that could give a hacker access to an Oracle database.

...

Litchfield's attack targets the Procedural Language/SQL programming language used by Oracle developers.

...

"If you happen to be using Oracle and you write your own applications on it, then yes, you could be writing vulnerable code," he said.

...

HP-UX Perl

This is a quick guide to using Perl on HP-UX and installing the interfaces to an Oracle database and MS Excel output.
In some environments it may be difficult to have additional Perl modules installed by the system administrators. This guide will take advantage of the default Perl installation, and install additional module as a non-root end user.

Perl source code can be downloaded and compiled. It is often more convenient to download a precompiled binary. Recent versions (11 and 11i) of HP-UX include Perl, which is a sufficient start for most scripting.

Determine your version of HP-UX with the uname command:

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

The Perl interpreter may already be in your PATH environment variable:

$ which perl
/usr/bin/perl

/usr/bin/perl is a soft link to /opt/perl.

$ ls -l /usr/bin/perl
lrwxr-xr-x 1 bin bin 18 Sep 28 2005 /usr/bin/perl -> /opt/perl/bin/perl

There is also an /opt/perl_64.

$ ls -ld /opt/perl*
dr-xr-xr-x 6 bin bin 96 Sep 28 2005 /opt/perl
dr-xr-xr-x 6 bin bin 96 Sep 28 2005 /opt/perl_64

Determine the version.

$ perl -v

This is perl, v5.8.2 built for PA-RISC1.1-thread-multi
(with 25 registered patches, see perl -V for more detail)

Copyright 1987-2003, Larry Wall

Binary build 808 provided by ActiveState Corp. http://www.ActiveState.com
ActiveState is a division of Sophos.
Built May 3 2005 14:06:55
<...>

"perl -V" will display details of what was compiled.

Oracle9i Database: Performance Tuning, 1Z0-033

The Oracle9i Database: Performance Tuning is the second of two required tests to achieve Oracle Certified Professional status.

These notes are from OCP Oracle 9i Database: Performance Tuning Exam Guide by Charles Pack.

V$SYSTEM_EVENT
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all the event waits on the instance since it started. It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the waits, you can determine the waits on the system in a given time interval.

V$WAITSTAT
This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.

V$SYSSTAT
This contains overall statistics for many different parts of Oracle, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.

V$FILESTAT
This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
V$TEMPSTAT
This view contains information about file read/write statistics.

V$LATCH
This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for.
V$LATCH_PARENT
V$LATCH_PARENT contains statistics about parent latches. The columns of V$LATCH_PARENT are identical to those in V$LATCH.
V$LATCH_CHILDREN
There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query the V$LATCH_CHILDREN view.


Methods for collecting statistics:
analyze
dbms_utility.analyze_schema
dbms_stats


V$SGASTAT
This view contains detailed information on the system global area (SGA).
SGA contains shared pool (library cache, dictionary cache, sometimes UGA).
If using MTS, configuring large pool can reduce library cache fragmentation.
V$LIBRARYCACHE
Each row in this view contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:
* SQL AREA
* TABLE/PROCEDURE
* BODY
* TRIGGER

V$DB_OBJECT_CACHE
This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool.

V$SQLAREA contains up to 1000 characters of the SQL, along with execution statistics.
V$SQLTEXT contains the entire SQL.
V$SQL_PLAN contains the actual plan. It could be useful to capture v$sql_plan for troubleshooting purposes when a SQL stops performing as expected.

DBMS_SHARED_POOL.KEEP will pin objects in the shared pool so they do not age out. alter system flush shared_pool will not clear pinned objects.
SHARED_POOL_RESERVED_SIZE provides space for larger memory allocations and defaults to 5% of the shared pool.
V$SHARED_POOL_RESERVED shows space used, misses, etc. If request_failures > 0 then increase shared_pool_size.

V$ROWCACHE dictionary cache statistics. Usually tuned by increasing shared_pool_size.
UGA, User Global Area. Contains user session data and cursor state. When using shared server, this moves from PGA to shared pool.
LARGE_POOL_SIZE Large pool, when configured, is used for RMAN IO buffers, parallel query message buffers, and UGA when using shared server. Does not have an LRU. Setting parallel_automatic_tuning=true will enable large pool.
If library cache hit is 100%, may set cursor_space_for_time=true.
When using star transformations or DSS queries, use cursor_sharing=exact.


SQL sorts and parallel reads bypass buffer cache, which implies the buffer cache does checkpoints to keep the buffer cache in sync.
Full table scan blocks are placed at end of LRU list.
Buffer cache read of block:
- reading process uses hash function to look for block in buffer cache
-- if found, block moves to MRU
-- if not found, LRU is searched for free buffer, block is read from disk and placed in free buffer
-- if block read from disk is too recent for query read consistency, look in rollback
DBWn writes when:
- dirty list length exceeds threshold
- free block not found in LRU
- every 3 seconds
- LGWR checkpoint
- alter tablespace begin backup / offline
- object is dropped
- shutdown normal / immediate / transactional

DB_CACHE_ADVICE enables statistics gathering for V$DB_CACHE_ADVICE.
SGA_MAX_SIZE is upper bound of dynamic SGA growth.
V$BUFFER_POOL general info for all buffer pools.
V$BUFFER_POOL_STATISTICS statistics for all buffer pools.

LRU latch contention:
cache buffer lru chain
The cache buffer lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained. Set to 1/2 number of CPUs. Monitor with v$system_event.
Cache buffer chains protect the list and are used when using the list. Monitor v$latch_children for high misses or sleeps.



Redo Log Buffer is a circular buffer in the SGA that holds information about changes made to the database.
- set to at least 1MB
- no real penalty for setting a little too high
- v$sysstat "redo buffer allocation retries" should be near zero
- v$session_wait "log buffer space" should be zero

JAVA_POOL_SIZE
JAVA_SOFT_SESSIONSPACE_LIMIT writes warning to trace file.
JAVA_MAX_SESSIONSPACE_SIZE hard limit that kills session.
DBWR_IO_SLAVES can simulate async IO.
DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).


When using workarea_size_policy=auto and pga_aggregate_target, do not set:
sort_area_retained_size
bitmap_merge_area_size
create_bitmap_area_size
hash_area_size.
V$PGASTAT monitors PGA usage.
V$SYSSTAT where name like 'sort%' for sorts to memory/disk.
V$SORT_SEGMENT info on every sort segment.
V$SORT_USAGE.username shows usage by user.



V$LATCH stats for SGA latching.
Wait event "latch free" is when a process tries for a latch and can not get it, then it sleeps. It spins on multi-cpu machines.
If "willing to wait", the process tries and then spins.
Latch request is "immediate" when it already has one or more latches and needs another. If this latch is not obtained, it releases all latches and starts over.



V$ROLLNAME has names of online rollback segments.
V$ROLLSTAT rollback segments statistics.
V$TRANSACTION useful for joining on UndoSegmentNumber.

What generates undo:
delete generates the most
inserts only writes the rowid
update writes row and index.

V$UNDOSTAT undo consumption.

Automatic undo pfile parameters:
undo_management
undo_tablespace
undo_suppress_errors
undo_retention

How to switch to a new undo tablespace:
alter system set undo_tablespace=___________
Existing transactions will continue to use the previous tablespace.



PMON Process MONitor cleans up aborted transactions and their locks.

DML_LOCKS=0. Enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.

delayed block cleanout - lock on the row is cleaned by the next transaction doing DML on the row. The lock is not cleaned from the row when the original locking transaction does a commit.

V$LOCK - This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.
V$ENQUEUE_LOCK - This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK.
V$LOCKED_OBJECT - This view lists all locks acquired by every transaction on the system.
DBA_BLOCKERS - DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS - DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.
utlloctt.sql shows locks in tree display.
DBMS_LOCK allows explicit sequential processing.
breakable parse lock A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.



Shared Server
Configure:
Oracle Net - tnsnames or other naming service.
init.ora - circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions

v$shared_server_monitor
v$dispatcher
v$dispatcher_rate
v$queue contains information on the shared server message queues.

User Global Area moves to the shared pool or large pool.
Dispatchers are limited to ~1000 connections each.
Startup/shutdown still requires a dedicated server connection.



Application Tuning
Index cluster contains nulls. No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.
hash cluster - direct row access via algorithm.
cluster good for - minimal key updates, tables frequently joined.

B tree index - balanced, when leaf block fills it is split in half, optionally can compress repeated values.
B tree reverse key index - can be useful with sequentially increasing keys to avoid last page contention and excessive index heights. Only useful for exact key lookups, can not be used for range scans.
Function based index - B tree or bitmap. Need query rewrite to use it.
Index Organized Table - requires primary key.
ROW OVERFLOW can split out non-PK columns.
contains logical row ID that is guess to row overflow.
may be partitioned.
may have bitmap index by using a mapping table.

Materialized View
refresh:
complete - truncate and run query.
fast - apply changes via materialized view log.
force - uses fast if possible, else uses complete.
on commit - automatic refresh after transaction commit.
on demand - manually call dbms_mview.





segment <- extent <- block <- os block
Initial and next extent defined at time of object creation or defaults to tablespace settings.

dbms_space_admin works on locally managed tablespaces.

High water mark is stored in segment header.
High water mark is raised in five block increments.
alter table table_name deallocate unused will drop high water mark if possible.
Temporary segments must use default block size.
PCTFREE how much table space to reserve for future row updates.
PCTUSED when to put table block on free list.
row chaining - row does not fit in an empty block so it is split across blocks.
row migration - when updated row won't fit in it's block and entire row is moved with a pointer remaining.
row migration - seen in statspack as "table fetch continued row" and in v$sysstat.
alter index index_name coalesce consolidates leaf blocks.
alter index index_name rebuild online moves the index so new storage parameters may be specified.
v$object_usage stores results of monitoring table and index access.




Outlines are stored in schema OUTLN.
Outlines are grouped into categories.
DBA_OUTLINES describes all outlines in the database.
DBA_OUTLINE_HINTS describes the set of hints that make up the all the outlines in the database.

Create outlines for all SQL:
alter system set create_stored_outlines=
alter system set create_stored_outlines=FALSE; # Turns it off.

create or replace outline myoutline on SELECT ...

Package DBMS_OUTLN.

Use the ALTER OUTLINE statement to rename a stored outline, reassign it to a different category, or regenerate it by compiling the outline's SQL statement and replacing the old outline data with the outline created under current conditions.
alter outline myoutline rebuild;


Private outlines:
exc dbms_outln_edit.created_edit_tables
create private outline newoutln from oldoutln; # Clones the outline.

alter session set use_private_outlines=true;
create or replace outline ____ from private ____; # Publish it.

DBMS_STATS:
height-based
valued-based
histogram good for skewed data.

DBMS_STATS.create_stat_table
DBMS_STATS.export_schema_stats
DBMS_STATS.import_schema_stats

dbms_system.set_sql_trace_in_session(SID=>' ', serial#=>' ', sql_trace=>TRUE)

explain plan set statement_id = ' ' for SELECT ...
set autotrace traceonly explain;

v$sql_plan has actual execution plan.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.




RESOURCE MANAGER
Consumer groups contain like users.
One plan directive may be active in the instance at a time.

The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives.
create_pending_area
create_plan
create_simple_plan (does it all in one step)
create_consumer_group
create_plan_directive
validate_pending_area
submit_pending_area

dbms_resource_manager.switch_consumer_group_for_sess/user

dbms_resource_manager_privs.grant_switch_consumer_group (grant to user not role)
dbms_resource_manager_privs.set_initial_consumer_group

alter system set resource_manager_plan= ____;
In pfile, the parm is resource_manager_plan.

v$rsrc_plan displays the names of all currently active resource plans.
v$rsrc_consumer_group displays data related to currently active resource consumer groups.
DBA_RSRC_PLANS lists all resource plans in the database.

Sun Grid Engine

The April edition of Sys Admin magazine provides an introduction to Sun's open source job scheduler, Grid Engine. While able to effectively coordinate compute tasks across many machines, it also handles job dependencies and could provide pressure to vendors such as AppWorx.

HP-UX Network Interface Check Duplex

How to check duplex setting of network interface on HP-UX 11.11. "-x" is the interface.

bash-3.00# uname -a; lanscan -q ; lanadmin -x 13;lanadmin -x 15
HP-UX host001 B.11.11 U 9000/800 1288319306 unlimited-user license
10
9
900 11 0
901 15 13
902
903
904
Speed = 1000 Full-Duplex.
Autonegotiation = On.

Speed = 1000 Full-Duplex.
Autonegotiation = On.

$ /usr/sbin/lanadmin


LOCAL AREA NETWORK ONLINE ADMINISTRATION, Version 1.0
Mon, Mar 19,2007 09:04:59

Copyright 1994 Hewlett Packard Company.
All rights are reserved.

Test Selection mode.

lan = LAN Interface Administration
menu = Display this menu
quit = Terminate the Administration
terse = Do not display command menu
verbose = Display command menu

Turn Off Oracle Index Parallel Query

Parallel query can be useful for reducing elapsed run time of Oracle index scans. In some circumstances, parallel query can cause longer elapsed run times.

As the schema owner, determine the index parallel query settings:

SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

DEGREE INSTANCES COUNT(*)
--------- ---------------------------------------- ----------
0 0 4
1 1 1303
6 1 8
10 1 3
DEFAULT 1 38

Run SQL to create a SQL file that will set noparallel:

$ cat bill_ix_alter_nopq_gen.sql
set pages 0 lines 100 feedback off
spool bill_ix_alter_nopq.sql
prompt spool bill_ix_alter_nopq

select 'alter index ' || index_name || ' noparallel;' from user_indexes where degree > '1'
order by 1
/
spool off

Run the generated SQL file (bill_ix_alter_nopq.sql).

Verify the new settings:

SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

DEGREE INSTANCES COUNT(*)
--------- ---------------------------------------- ----------
0 0 4
1 1 1352

Monitoring Oracle Data File For Can't Extend

There are several pitfalls when creating SQL that will monitor for a can't extend condition in the Oracle database.

This appears to show a data file larger than the maximum size.

SQL> select substr(FILE_NAME,22,23) as data_file_name, bytes, maxbytes, maxblocks, USER_BYTES, AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_BILL_INV_TAX'

DATA_FILE_NAME BYTES MAXBYTES MAXBLOCKS USER_BYTES AUT
------------------------- ---------------- ---------------- ------------ ---------------- ---
cust_bill_inv_tax02.dbf 34,129,051,648 34,359,721,984 4,194,302 34,078,720,000 YES
cust_bill_inv_tax01.dbf 34,358,689,792 34,358,689,792 4,194,176 34,078,720,000 YES
cust_bill_inv_tax03.dbf 16,532,635,648 2,147,483,648 262,144 16,252,928,000 YES

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

Oracle Note:197244.1 explains this situation.
The MAXBYTES column in DBA_DATA_FILES is not updated when the datafile has been resized. The only column that is updated is BYTES. The MAXBYTES is updated by the ALTER DATABASE command with MAXSIZE option as described above.

Alternative To SELECT_CATALOG_ROLE In Oracle

It may be useful to allow developers to look at the plsql (views, procs, etc) for a schema without granting the SELECT_CATALOG_ROLE role. This view will provide access, with the possible drawback of adding an object to the SYS schema.

Run this as sys, like:

sqlplus "/ as sysdba" @script.sql

CREATE OR REPLACE VIEW SYS.plsql_ASCHEMAOWNER (OWNER,NAME,TYPE,LINE,TEXT) AS
select OWNER,NAME,TYPE,LINE,TEXT
from sys.dba_source
where owner='ASCHEMAOWNER';

create or replace public synonym plsql_ASCHEMAOWNER for sys.plsql_ASCHEMAOWNER;
grant select on sys.plsql_ASCHEMAOWNER to public;

desc plsql_aschemaowner
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

Basic Oracle Role For All Users

Prior to changes in Oracle 10, an "enduser" role was useful in every database. The role alllows a connecting and a few harmless though useful grants. Every new user would have this role.

CREATE ROLE "ENDUSER" NOT IDENTIFIED;
GRANT ALTER SESSION TO "ENDUSER";
GRANT CREATE SEQUENCE TO "ENDUSER";
GRANT CREATE SESSION TO "ENDUSER";
GRANT CREATE SYNONYM TO "ENDUSER";
GRANT CREATE VIEW TO "ENDUSER";

Oracle Database Go-Live Check List

Items to review before production go-live of an Oracle database system.

+) Security
accounts (check users with roles: DBA, import any, ...)
listener password
(if non-DBAs can log on to DB machine) check file Oracle directory and permissions
check for default DB accounts that can be locked or have the passwords changed
change OS passwords
change at least DB system password

+) Automate instance/listener start and stop with the operating system startup "rc" scripts.

+) Backup/recovery
verify archive log mode is on
verify rman jobs are scheduled and running correctly
in case backup system is down, ensure a couple days of disk space for archive_log_dest
export schema rows=n weekly or monthly
depending on requirements and performance considerations, enable db_block checksum

+) Performance
configure resource governor to guide CPU usage and parallel query usage
configure resource governor to limit undo usage by non-application owner accounts
schedule statspack snap every twenty or thirty minutes
enable monitoring on application tables
schedule dbms_stats.gather_schema_stats to gather stale (usually weekly or monthly)
use dbms_stats to create and export a statistics table (useful to quickly restore older version of statistics)

+) Checks
tablespace that will not be able to grow
undo tablespace growth is capped at less than the size of the file system
alert log error messages
users with default tablespace of system
operating system error log "syslog"
network error (netstat -i)

+) Multiple temp tablespaces
one tablespace that is only available to application owner
other tablespaces to segregate other users from interfering with application temp usage

Disk Usage By User

In shared unix filesystems, like /tmp, it is often useful to find who is using the most disk space.

While

du -sk *

will find large files,

find . -user mc -type f -exec du -k {} \; | awk '{ s = s+$1 } END { print "Total used: ",s }'

will show which user is consuming the most space.

IBM has an article describing this combination of find, du, and awk.

Locating Perl Modules

If perl modules are installed and perl is complaining of not being able to find the modules, try one of these:


1) create environment PERL5LIB, which works similar to the PATH environment variable


2) in the perl code, add:

BEGIN {unshift @INC, "/home/user/perl/modules/lib"};

3) in the perl code, add:

use lib '/home/user/perl/modules/lib';

Oracle User Logon Trigger

It can be useful to set session settings for a user in a logon trigger. A user may want to override the setting for a specific parameter such as star_transformation_enabled, or it may be necessary to turn on sql_trace for a user.

create or replace trigger logontrigger after logon on schema
declare
p_session_user varchar2(30);
p_host varchar2(200);
p_sql varchar2(230);
p_ip_address varchar2(30);
p_sessionid number(30);
begin
select translate(sys_context('userenv', 'host'), '-\ /', '____') into p_host from dual;
select sys_context('userenv', 'session_user') into p_session_user from dual;
select sys_context('userenv', 'sessionid') into p_sessionid from dual;
select sys_context('userenv', 'ip_address') into p_ip_address from dual;

-- Create trace file with a recognizable name.
p_sql:='alter session set tracefile_identifier=' || p_session_user || '_' || p_host;
execute immediate p_sql;

-- Choose one of the following trace levels. The second form provides bind variables and the result set.
p_sql:='alter session set sql_trace=true';
--select 'alter session set events ' || '''10046 trace name context forever, level 12''' into p_sql from dual;
execute immediate p_sql;

end;
/

show err
prompt
prompt Remember the user needs 'alter session' privilege.
prompt To remove: drop trigger logontrigger

After creating the logon trigger, log on as the user. If it does not generate a file in the user jump destination, then check the alert.log for errors.

Oracle Data Migration Via SQL Script

Data changes are often promoted to production environments via scripts that are stored in a change control system.

If using a SQL script to apply data changes, these are useful to include:

-- The output file name. DO NOT include a path nor a drive letter.

spool outputFileName

prompt Include info here about defect tracking number, developer, etc.

-- On error rollback and exit. This is generally only needed for scripts that will be scheduled. For an ad-hoc migration, this is not needed.
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback

-- Set the date format to avoid relying on defaults that are platform-specific.
alter session set nls_date_format='MM/DD/YYYY';

-- set echo on will print the SQL to the screen. This is optional and may not be useful if there is too much output.
set echo on

-- set define off will disable interpreting of the ampersand character.
set define off

-- Select the current time to show when script was implemented.
select systimestamp from dual;

-- set timing on will print elapsed time of each command.
set timing on

-- place script contents here.
UPDATE tablename set columna=value1;

-- spool off will stop logging to the output file.
spool off

For change control purposes, the migrations to production may run via sqlplus. Be aware that sqlplus has a limit to the number of lines per SQL, while tools such as Benthic appear to not have a reasonable limit to the number of lines per SQL. What may be encountered is a developer testing a script via Benthic with 5000 or more lines per statement (typically a large IN list) that will not run via sqlplus. The error message will be:

ERROR at line 565:
ORA-00933: SQL command not properly ended

Become Another Oracle User

This technique is useful to log in as another user to troubleshoot a problem they may be having. Oracle DBA privileges are needed.

Open two windows.

In the first window:

select password from dba_users where username='THEUSER'
alter user THEUSER identified by pass

In the second window:

sqlplus THEUSER/pass

In the first window:

alter user THEUSER identified by values 'password values from first query'

In the second window you will be logged on as the user. If the password can be set and reset quickly, this can be used for troubleshooting on a live production system.

Multi-Platform File Salvage

The PhotoRec program is multiplatform and can recover dozens of file types on corrupted or "formatted" file systems.