Tuesday, December 27, 2005

Deprecate Oracle Control Files

The Oracle control file is an operating system file that Oracle relies upon for startup and recovery. The file is essential for Oracle operation, and it is not managed as a tablespace is managed - no transaction consistency or redo log on the changes to the file.

The pfile or spfile (commonly called initSID.ora) specifies the location of the control file. The instance uses the control file specified in the pfile/spfile to open the database.

Having an external file that is not managed like a typical oracle datafile (a datafile that backs a tablespace) adds complexity during backup and recovery. There are specific procedures for backing up and recovering a control file.

Database information in the control file is also contained in the SYSTEM tablespace. Oracle documentation suggests checking the error log for indications of inconsistency between the SYSTEM tablespace and control file. This implies they contain the same information.

It would be possible to have the pfile/spfile point to the datafile that backs the SYSTEM tablespace and start the database by opening the SYSTEM tablespace. Removing control files from the Oracle architecture seems to be straight-forward and would reduce complexity. DB2, Sybase, and MS-SQL don't have anything like the control file, so it is not a technical requirement.

One kink is that Oracle writes RMAN information to the control file. This information could stay in the control file or be moved to a new file (named rman history file?).
The control file has been around for so many years that Oracle likely sees no marketing advantage in removing it to clean up the architecture.

Sun Certification Discount Through January

To qualify for the 10% discount offer, purchase a qualifying Sun certification exam, assignment, or essay by January 31, 2006. Provide Priority Code WW26CXG at the time of purchase.

I have found Bill's books useful in the past.

Free study guide.

Monday, December 26, 2005

Web Cam Image Publisher

This software monitors a web cam and can upload images to a web site. It has many more features and is much more stable than the software shipped by Creative with their web cam. Free download.

Sunday, December 25, 2005

Microsoft Office Alternative

OpenOffice has been around for a few years. I mostly install it on unix systems, as the MS Windows systems usually have a site license for the Office Suite. OpenOffice install on unix is straight forward, and there are ports for many operating systems. Version 2.0.1 is available.

Sun is supporting OpenOffice through the StarOffice Suite.

Tuesday, December 20, 2005

Open Source Virus Scanner

We have been using the open source virus scanner ClamAV since 2004. It is multi-platform, with a web site specifically for the Microsoft Windows version. It has a scheduler to scan files and to download updates.
On XP, memory usage for the Windows background task is about 2,428K with a VM size of 14,408K.

Monday, December 19, 2005

Power Usage and Postgres

Sun has been moving to reduce power consumption in the data center. Anyone managing more than a dozen machines must be concerned about scalability and redundancy of their power and cooling. More efficient chips are good.

Sun also announced support for Postgres, which will encourage more companies to consider open source databases. Low end databases have become a commodity. Money can still be made in support, while licensing fees are under pressure.
Microsoft is trying the opposite by raising prices. Like GM and Ford have found, consumers may pay up for a couple years, but consumers will look for alternatives.

Saturday, October 29, 2005

Another Alternative Browser

Mozilla and Firefox are good alternatives from Internet Explorer. Another alternative browser that I have used for several years is Opera. Opera has had tabbed browsing and gesture-based navigation for a few years. The Opera browser is now free.

Thursday, April 28, 2005

Oracle Pin Frequently Used Objects

Some people like to pin frequently used objects while other people rely on the Oracle database to do it's job of keeping frequently used objects in memory. In case you feel the need to find objects to pin in memory, you may refer to this code.


Example crontab to schedule the SQL job:
1 0,2,4,8,10,14,16,18,20,22 * * * . /home/user/.profile;
sqlplus -s '/ as sysdba' @/home/user/script/whatToPin >>
/home/user/output/whatToPin.out


Example SQL job:
set lines 200
set pages 200

col host_name form a10 head "Host Name"
col systimestamp form a40
select instance_name, host_name, systimestamp from dual, v$instance;

col name form a44
col type form a20
col executions head "Execs > 999"
SELECT owner||'.'||name name
, substr(type,1,12) type
, executions
, sharable_mem
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
and executions > 999
and owner='your_application_schema_owner'
and kept='NO'
ORDER BY executions desc, sharable_mem desc
/
exit


Example job output:
INSTANCE_NAME Host Name SYSTIMESTAMP
---------------- ---------- ----------------------------------------
BILQA qhus021 20-MAR-06 01.07.14.134548 PM -10:00


NAME TYPE Execs > 999 SHARABLE_MEM
-------------------------------------------- --------------------
----------- ------------
owner.CMF_NOTES_BIUTRIG TRIGGER 351102 6575
owner.CMF_RTRIG TRIGGER 107031 26111
owner.BIP_GET_CONTRACTS PROCEDURE 21243 24359
owner.LTP_GET_FILES PROCEDURE 10266 19779
owner.GET_SYSTEM_PARAMETER_INT PROCEDURE 10164 19102
owner.BIP_GET_DISCOUNT_TARG PROCEDURE 9408 18939
owner.BIP_GET_DISCOUNT_REST PROCEDURE 9408 18707
owner.BIP_CREATE_BILL_INVOICE_TAX PROCEDURE 9238 21761
owner.MPS_NUM_USAGE_TYPES_SPLIT PROCEDURE 3960 19071
owner.MPS_NUM_USAGE_TYPES_UNITS PROCEDURE 3960 19039
owner.BIP_CREATE_BILL_INVOICE_DETAIL PROCEDURE 3261 28028
owner.FILE_STATUS_RTRIG TRIGGER 3153 6703
owner.GET_INSERT_SEQ_NUM PROCEDURE 2126 22880
owner.GET_SEQ_NUM PROCEDURE 2119 31705
owner.MCAP_UPD_USG_FILE_STATUS PROCEDURE 2054 20782
owner.CUSTOMER_SERVICE_CENTERS_RTRIG TRIGGER 2050 6612
owner.ORD_ITEM_BUIR_TRIG TRIGGER 2027 28848
owner.ORD_ITEM_ATRIG TRIGGER 2027 8764
owner.ORD_ITEM_ARTRIG TRIGGER 2027 6381
owner.GET_SYSTEM_PARAMETER_STRING PROCEDURE 1675 18953
owner.MPS_GET_RB_RATE_USAGE PROCEDURE 1587 21963
owner.PRODUCT_VIEW_ACTRIG TRIGGER 1494 43465
owner.HT_PRODUCT_AIU_FER TRIGGER 1494 12312
owner.PRODUCT_VIEW_BIUTRIG TRIGGER 1494 7530
owner.PRODUCT_VIEW_ATRIG TRIGGER 1269 9736
owner.LTP_LOCK_FILE PROCEDURE 1093 20467
owner.MPS_UPD_FILE_STAT_ENTRY1 PROCEDURE 1063 21934
owner.MPS_LOCK_INPUT_FILE1 PROCEDURE 1063 20866
owner.MCAP_ADD_USG_FILE_STATUS1 PROCEDURE 1063 20647
owner.LTP_UNLOCK_FILE PROCEDURE 1063 20581
owner.CAP_GET_PREV_FAIL PROCEDURE 1063 19879
owner.MPS_GET_ERR_STATS PROCEDURE 1063 19183
owner.FILE_STATUS_ADD_ENTRY PROCEDURE 1027 21459
owner.MCAP_LOCK_INPUT_FILE1 PROCEDURE 1027 21131
owner.COM_UNLOCK_USAGE_FILE PROCEDURE 1027 20843
owner.MCAP_UNLOCK_INPUT_FILE1 PROCEDURE 1027 20421
owner.MCAP_EXT_CONTACT_GET_BY_ID PROCEDURE 1027 19768
owner.FILE_STATUS_GET_BY_NAME PROCEDURE 1027 19405
owner.MCAP_GET_SERVER_IDS PROCEDURE 1027 18673

39 rows selected.