Feed aggregator

Second Life client running on Solaris x64 - contd

Siva Doe - Thu, 2007-01-11 20:38

As promised, here is the update.

For Mads and whomever is interested in building the Second Life client on Solaris (x64), this is what I did.
Please do remember, these are just to get the client build on Solaris. I havent completely run the client yet (nothing beyond the login screen). So, buyer beware.
Basically, I followed the Linux instructions in Second Life Twiki page.
Downloaded and built all the libraries mentioned and copied the libraries and headers in the directory under, 'i686-sunos5', instead of 'i686-linux'
Modified SConstruct  (for scons building).

  • Look for 'linux' and introduce the code for 'sunos5' (replace -DLL_LINUX with -DLL_SOLARIS)
  • Remove the 'db-4.2' entry under libs line.
  • Replace 'yacc' with 'bison -y'; 'lex' with 'flex'; 'g++-3.4' with 'g++' (under /usr/sfw/bin); 'strip' with 'gstrip'

Whichever subdirectory contains 'files.linux.lst', make a copy of it called 'files.sunos5.lst'.
Then comes the code changes. Basically, search for files containing 'LL_LINUX' and add "|| LL_SOLARIS" or "&& ! LL_SOLARIS" as appropriate.
In llcommon/llpreprocessor.h:38, I added  "|| (defined(LL_SOLARIS) && !defined(__sparc))" to the line to set the ENDIAN correctly.
In llcommon/llsys.cpp, I added code to use the output of 'psrinfo -v', instead of reading from '/proc/cpuinfo' for SOLARIS.  Similarly, used 'getpagesize() \* sysconf(_SC_PHYS_PAGES)' to get the "Physical kb". I know there are better ways, but just wanted to get the build completed.
In llmath/llmath.h, I was running into some problems regarding 'isfinite'. I replaced with this.
#define llfinite(val) (val <= std::numeric_limits<double>::max())
The other significant work is in 'llvfs/llvfs.cpp' and 'newview/viewer.cpp'. Replaced the code for 'flock' with the appropriate 'fcntl' code.
In files 'newview/lldrawpoolsky.h' and 'newview/llvosky.cpp', replace the variable 'sun' with 'Sun'. 'sun' in a SunOS is defined already, of course.
In 'newview/viewer.cpp', rewrote the 'do_basic_glibc_backtrace()' to use 'printstacktrace()' instead.
Well, you can follow the above instructions, or send me a mail, I will send the diff output. :-)

For runtime, you will have to set your LD_LIBRARY_PATH as mentioned in the Twiki page.
Thats all I have for now. Will update later, if any.


PS: It is indeed a pity that I cant login to a server named after me ;-) (userserver.siva.lindenlab.com)

Increasing the Longevity of Your CPU

Eric S. Emrick - Thu, 2007-01-11 18:42

One of my current assignments is to evaluate the potential to increase CPU headroom on a server running a large OLTP Oracle database. Of course, any project such as this is typically motivated by the desire to save money by foregoing a seemingly imminent hardware upgrade. Realizing more CPU headroom for your Oracle database server can be achieved, but not limited to, the following approaches:

1. Add more same-speed CPUs to your existing server.
2. Replace your existing CPUs with faster CPUs.
3. Replace your existing CPUs with a greater number of faster CPUs.
4. Commission a new server platform with more same-speed and/or faster CPUs.
5. Commission a new server platform with a greater number of slower CPUs.
6. Chronologically distribute the load on the system to avoid spikes in CPU.
7. Reduce the work required of the system to satisfy the business.

More times than not I suspect approaches 1-5 are chosen. I am of the opinion that 1) and 3) are more predictable when trying to evaluate the expected CPU headroom yield. Propositions 2), 4) and 5) can be a little less predictable. For example, if I double the speed of my current CPUs will I yield the upgraded CPU cycles as headroom? That is, if I am running 10x500MHz and upgrade to 10x1GHz will I now have the additional 5GHz as headroom? It has been my experience that upgrades such as these do not produce such predictable results, especially if your current box approximates 100% utilization. Certainly, moving to a new server with a greater number of same-speed and/or faster CPUs is a tricky proposition. New servers need to be tested using Production volume with great rigor. While at face value 500MHz would appear to be universally “portable” to any server, there are many other factors that can influence your CPU horsepower: memory architecture, amount of processor cache, processor crosstalk, etc. Options 1-5 can all be very costly and in some cases yield undesirable and unpredictable results.

If you have the luxury of distributing the load on your system to avoid spikes in CPU then that is a great first option. It could buy you more time to evaluate a longer-term solution. For example, shifting any batch jobs to off-peak OLTP hours might give you immediate relief.

What if we can simply “do less” to satisfy the needs of the business? This concept is not new to most Database Administrators and rings a rather cliché tone. After all, aren’t we brow-beaten by the dozens of books and countless articles that speak to database and SQL optimization? The “do less” principle is very sound, but it can be intractable. Reducing the work required of an application often requires management support and can run into political obstacles at every turn. Getting Application Developers and Database Administrators to work in lockstep can require a significant effort. If Management, Developers and Database Administrators buy into a synergistic endeavor the benefits can be amazing – and can save the company a large sum of money.

If you are lucky enough to be working on a project where the common goal of all parties is to reduce the CPU load on your system then I have learned a few things that I hope can help you.

Identify the Targets for Optimization

Identify those SQL statements that contribute the greatest to the CPU load on your database server. These statements usually relate to those that produce the most logical I/O on your database. Caution needs to be taken when trying to identify these statements. You shouldn’t focus solely on those statements that have the highest logical I/O (LIO) to execution ratio. Often you will find statements that are well optimized but are executed with extremely high frequency. Look for the aggregate LIO footprint of a SQL statement. Without Statspack or AWR this analysis might be very difficult. However, if you collect this diagnostic data you can use the LEAD analytical function to craft a nice SQL statement to identify the top CPU consuming statements on your system (join stats$sql_summary and stats$snaphot).

Don’t limit your SQL statement identification to just those statements flagged by your analysis as a top CPU consumer. Go another step and identify the most frequently executed statements. Some of the most frequently executed statements are the most optimized on your system. These statements if executed by many programs concurrently can influence concurrency and thusly CPU load. One approach I took recently identified the top 20 CPU consuming statements during a 12 hour window of each week day. I then ran the same analysis against the most frequently executed statements on the system. The results yielded only 31 distinct statements as 9 were on both lists. The amazing thing is that, on average, these 31 statements contributed to 58% of all logical reads on the system and 59% of all executions. Keep in mind that there were over 20 thousand distinct statements cached in the Shared Pool. It is rather amazing that such a small subset of the application footprint contributed so greatly to the aggregate load.

Ask The Right Questions

The identification phase is crucial as you want to optimize that which will yield the greatest benefit. Subsequent to the identification phase the Database Administrators and Developers can sit and discuss approaches to reduce the load incurred by these SQL statements. Here are some of the key points I have taken away during such collaborative efforts.

1. Is there a better execution plan for the statement? Optimization is often achieved by rewriting the query to get at a better execution plan. While I don’t like hinting code, they can relieve pressure in a pinch.

2. Does the statement need to be executed? If you see SQL statements that seldom/never return rows (rows/exec approaches 0) there is a possibility it can be eliminated from your application.

3. Does the statement need to be executed so frequently? You might be surprised that Developers often have other application-side caching techniques that can dramatically reduce the frequency of a statement’s execution against the database. Or, the application might simply call the statement needlessly. It doesn’t hurt to ask!

4. Are the requirements of the business immutable? Sometimes you can work an optimization by simply redefining what is required. This is not the tail wagging the dog here. It is possible that the business would be completely happy with a proposed optimization. For example, can the query return just the first 100 rows found instead of all rows.

5. Do the rows returned to the application need to be sorted? Highly efficient SQL statements can easily have their CPU profile doubled by sorting the output.

6. Are all columns being projected by a query needed? If your application retrieves the entire row and it only needed a very small subset of the attributes it is possible you could satisfy the query using index access alone.

7. Is the most suitable SQL statement being executed to meet the retrieval requirements of the application? Suitability is rather vague but could apply to: the number of rows fetched, any misplaced aggregation, insufficient WHERE clause conditions etc.

8. Are tables being joined needlessly? I have encountered statements that Developers have determined are joining a table, projecting some of its attributes, without using its data upon retrieval. The inclusion of another table in such a manner can dramatically increase the logical I/O required. This is extremely difficult for a DBA to discern without intimate application code knowledge.

9. How well are your indexes clustered with your table(s)? Sometimes data reorganization techniques can greatly reduce the logical I/O required of a SQL statement. Sometimes IOTs prove to be very feasible solutions to poor performing queries.

10. Can I add a better index or compress/rebuild an existing index to reduce logical I/O? Better indexing and/or index compression could take a query that required 10 logical I/O operations down to 5 or 6. This might feel like a trivial optimization. But, if this statement is executed 300 times each second that could save your system 1,500 logical I/Os per second. Never discount the benefit of a 50% reduction of an already seemingly optimized statement.

11. Can I reorganize a table to reduce logical I/O?

I suspect most of us have read that 80% of optimization is application centric (I tend to feel that the percentage is higher). Usually the implication is that the SQL being generated and sent to the database is 80% of the target for optimization. More specifically, optimization requires the tuning of SQL 80% of the time. However, don’t limit your efforts to optimize your application to “tuning the SQL.” Sometimes a portion of your optimization will include “tuning the algorithms” used by the application. Needless execution and improper execution of SQL statements can be equally destructive. Hardware resources, in particular CPUs, can be very expensive to purchase and license for production Oracle databases. It is well worth the effort to at least investigate the possibilities of increasing CPU headroom by decreasing CPU utilization.

Update: An astute reader suggested I mention Cary Millsap's Optimizing Oracle Performance with regard to this topic. I highly recommend reading this book as it weighs in heavy on Oracle optimization and Method-R. Trust me if you have optimization on the brain don't miss this read.

9i bug using Oracle Text to search XML data

Vidya Bala - Thu, 2007-01-11 13:05
Using Oracle Text to Search XML Data: XML Data inserted in DATA column in TEST table

Create table test(id integer,DATA CLOB)

Department name="CS"
Vidya Bala


Create an auto section group

ctx_ddl.create_section_group('myautosectiongroup', 'AUTO_SECTION_GROUP');

create index test_index on test(DATA)
indextype is ctxsys.context
parameters ('SECTION GROUP myautosectiongroup');

WHERE CONTAINS(DATA, 'Vidya WITHIN Employee') > 0;
1 Row Returned

WHERE CONTAINS(DATA, 'CS WITHIN Department@name') > 0;

0Rows (10g returns 1 row – 9i returns no row – Support is working on getting bug fix for the issue)
Categories: Development

Business Integration Journal changes its name - and the last BIJ contains my new article

Clemens Utschig - Wed, 2007-01-10 15:06
"Business Integration Journal (BIJ) is changing to Business Transformation & Innovation (BTIJ) to better reflect the current editorial content being published and new topics readers want covered.

Targeted at the intersection of business and IT, BTIJ carries on the “how-to” and “what-works” tradition of Business Integration Journal helping IT and business managers quickly adapt to changing business needs, continuously innovate successful new products and services, and consistently gain and maintain a competitive advantage.

In the last edition of the BIJ - as we know it - my article on SOA projects got published - and I am very proud of it.

"A World of Assumptions That Make an SOA Project the Perfect Storm—and What You Should Know About Them by Clemens Utschig-Utschig. This article shows that, while Service-Oriented Architecture (SOA) brings many advantages, it doesn’t alleviate the need for good planning, organization, and training."

The whole article can be found here

Second Life client running on Solaris x64

Siva Doe - Wed, 2007-01-10 14:31

Yes. I was able to build the recently open sourced Second Life client. It took me couple of days to get to this stage. To get it running took half a day. The reason being that ENDIAN was set to BIG for non-Linux boxes and that had me stumped for a long time. Of course, being behind SWAN firewall doesnt let the client connect to the server :-( Any one knows a way to achieve this?

Any way, here is the obligatory screenshot. Will keep posted on the updates.


Remote automated install of Oracle 10g client

Stephen Booth - Wed, 2007-01-10 06:04
We have a situation where we need to rationalise the range of installed Oracle clients (i.e. the bit that sits between the app and the network stack) we have installed. We currently have versions from 7.x through to 10.2 installed accross approximately 12,000 desktops (accross various locations in an area of around 26 square miles) running various apps on Windows versions from NT4 to XP (mostly Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com1

Auditing on FND_FLEX_VALUES: How to see Audit History

Jo Davis - Tue, 2007-01-09 21:23
To keep an audit trail be kept for mapping of segment values to external values (such as for extracts to other systems) and changes to it
1) Define DFF on FND_FLEX_VALUES to contain the mapping values
2) enabled auditing on FND_FLEX_VALUES,
3) if they wish to query the audit data start with this:
select *
where attribute1 is not null
FND_FLEX_VALUES_AC1 is a view of the audit table

How to Migrate Personalizations from TEST to PROD on Release

Jo Davis - Tue, 2007-01-09 20:35
This applies to iProcurement (11.5.10 only), iExpenses 2nd Generation (i.e. above 11.5.7 and the white screens, not the blue ones) and anything else that uses the new self-service architecture (i.e. NOT iTime or anything else with those blue screens and no bouncing balls across the top.... you know what I mean)

It's all covered in Metalink Note 370734.1 but suffice is to say

- it can be done, you don't need to redo all the personalizations

- you can only use this on release

- you will need the appltest and applprod passwords and a passing familiarity with Unix (or a tame developer or DBA to help you)

Have a great day


A new SOA year has begun - what will the SOA Santa bring?

Clemens Utschig - Thu, 2007-01-04 16:00
Wow, the new SOA year is here - and many cool things are about to arrive. But before looking into the future, let's look back into 2006 - and what happened there.

My first year, living in San Francisco and working within a wonderfull group, envisioning future and creating our next generation products went by - and I must admit it was far better than I thought it'd be.

First, and most important - we delivered SOA Suite - the first major, integrated SOA plattform. Not just integrated, but also with two new key components, the Oracle Enterprise Service Bus (ESB) and Rules. Since OOW 2006 we we presented the first public available release, our customers are using it heavily to build their next generation SOA - and according to many of them - they enjoy doing so :D

Also, we signed an OEM agreement with IDS Scheer, to build the ORACLE BPA Suite on top of it. Using Business Process Architect - for the first time Business Processes will make it all the road down to be executable, and all the way back to feed real world data into simulation and continous refinement.

Another also, was the overwhelming amount of SOA related sessions at Oracle Open World, and the great feedback we received. It was a blast, more than 40000 people made the city for one week being red, being Oracle. Elton John played during the big Oracle party, and so on.

Open SOA - www.osoa.org went live - and with it the big players in the SOA market space, to drive next generation, easy to use standards.

So after all what's in for 2007?

While we all work hard on delivering 11g - our next generation SOA infrastructure, based on Service Component Architecture (SCA) and supporting Service Data Objects (SDO) - we expect a patchset for (which will offer a ton of new features, and little fixes here and there).

BPEL 2.0 will see the light of life - after more than 2 years, and the last public review finished, some more polishing needs to happen - and then we are ready to go. With it - BPEL will present a number of long awaited cool features, such as the new variable layout ($inputVariable) and scoped partnerlinks.

Evangelism on SOA will continue to drive the spirit of SOA and help customers adopting to it - I really look forward to even more presence around the globe, and many customers going live on SOA Suite.

Personally for me - it's moving on with my SOA Best practices series on OTN, supporting people around the globe and the OTN forums, doing evangelism on SOA & help creating 11g. More than enough.

10G SQL Access Advisor and SQL Tuning Advisor

Vidya Bala - Thu, 2006-12-28 15:45
I was able to take advantage of the holidays to complete a 9i to 10g cross platform migration. While I was quite happy with the migration process (especially on how datapump has made this effort much easier), I was a little bit disappointed on what SQL Access and SQL Tuning Advisor had to offer.

There were a bunch of queries that we have been meaning to tune for quite some time , I thought I could create a tuning task with 10G SQL Tuning advisor and see if I could get some valuable recommendations.

The Recommendations I got were far from anything of significance (eg: add an index to a small lkup table).

I couldn’t help but wonder is there is much success/help using Oracle 10g SQL Access/Tuning Advisor in the industry
Categories: Development

Merry Christmas, Happy New Year, and a Poll

Marcos Campos - Sun, 2006-12-24 04:30
It has been a great year. My daughter was born as well as this blog. I have launched this blog at the beginning of the year (January first to be more precise) and the readership has been great. Amongst the posts, Time Series and Automatic Pivoting were probably the most viewed. I am on vacation in Brazil right now enjoying a family reunion. I have a big family and it is hard to get everyone Marcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com0
Categories: BI & Warehousing

ASM and NetApp Filer

Vidya Bala - Thu, 2006-12-21 15:37
Link to ASM and NetApp

I have been spending the last few days looking into what advantages we would have using ASM on a NFS mount as opposed to having the database files directly on NFS. If your on RAC then ASM is mandatory but for non RAC 10g instances and NetApp - ASM is not mandatory.

The biggest benefit I see is volume management features with ASM.

does this mean I can change volume sizes etc actually online ? may be IO balancing across different volumes an added feature. I am walking into a totally new area (ASM on any kind of direct attach storage I can for sure see it being beneficial on NFS I am not so sure?)

anybody with sucess stories?
Categories: Development

Cross Platform Migration 9i to 10g

Vidya Bala - Thu, 2006-12-21 15:05
Migration Procedure Implemented.

Cross Platform Migration SLES8) Standard Edition to 10g Rel 2 (Solaris 10)

This is a quick overview of a migration procedure I have just finished implementing on a test environment– If you see anything else in the procedure that should be added or should be noted, please feel free to post comments – as always there has been a lot of mutual learning and help from my blog readers.

Step1: Server A :
Clone Production Database to Preprod Environment. (Datafile,Redologfile,controlfiles all on Shared File System NFS).
Database Release :
Suse Linix version : SLES 8
Database Size : 50.89 G

Step2: Server B:
Install 10g Release 2 on a new SLES 9 server. Note 10g Release 2 is not supported on SLES8.
Make sure shared file systems on ServerA are mounted on Server B.
Copy parameter file from Server A to Server B . Make appropriate path changes to parameter file on Server B.
Database Release : 10g Release 2
Suse Linix version : SLES9

Step3: Upgrade 9i database to 10G
On Server B upgrade database to 10G

sqlplus /nolog
startup upgrade

CREATE TABLESPACE sysaux DATAFILE ' sysaux01.dbf'

Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

Run catupgrd.sql:

SQL> @catupgrd.sql

Run utlu102s.sql to display the results of the upgrade:

SQL> @utlu102s.sql

Turn off the spooling of script results to the log file:


Shut down and restart the instance to reinitialize the system parameters for normal operation.


Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

Exit SQL*Plus.

Total Time to Upgrade Database : 38 minutes

Step 4 – Upgrade 10g Database (using expdp)

Now that we have the database migrated to 10gRel2 on Server B(SLES9), we can export the database using 10g datapump. We will export only the Application Related Tablespaces. Tablespaces excluded are as below

INDX --- no application related objects in this tablespace

Before running the export – OWM and OLAP options need to be de-installed if not being used to avoid export errors

If the Oracle Workspace Manager feature is not used in this database: de-install the Workspace Manager:
SQL> @$ORACLE_HOME/rdbms/admin/owmuinst.plb

clean up AW procedural objects:SQL> conn / as SYSDBASQL> delete from sys.exppkgact$ where package = 'DBMS_AW_EXP';

Afterwards, run the export.

CREATE OR REPLACE DIRECTORY pump_dir AS 'xxxxxxxxxxxxxxxx';

Export only application related tablespaces

$ORACLE_HOME/bin/expdp system/manager tablespaces=\(t1,t2 \) directory=pump_dir dumpfile=pump.dmp logfile=pump.log

Full database export of 50+G database took about 80 minutes to export

Step5 – Prepare Target environment – Server C with 10g Release 2

Install 10g Release 2 on Solaris 10 Servers (SERVERC).

Installing Oracle Database 10g Products from the Companion CD
The Oracle Database 10g Companion CD contains additional products that you can install. Whether you need to install these products depends on which Oracle Database products or features you plan to use. If you plan to use the following products or features, then you must complete the Oracle Database 10g Products installation from the Companion CD:
· JPublisher
· Oracle JVM
· Oracle interMedia
· Oracle JDBC development drivers
· Oracle SQLJ
· Oracle Database Examples
· Oracle Text supplied knowledge bases
· Oracle Ultra Search
· Oracle HTML DB
· Oracle Workflow server and middle-tier components

On Server C use DBCA to create database creation scripts. Select the config parameters you need for your database as you go through the DCA wizard.

The scripts will create a standard database with no application related objects yet. Run create scripts to create database.

Tablespaces created (this is assuming none of the additional components were installed)

Make sure the Listener is up.
is the em console for the database

Note the below before proceeding with the em console
Oracle Enterprise Manager 10g Database Control is designed for managing a single database, which can be either a single instance or a cluster database. The following premium functionality contained within this release of Enterprise Manager 10g Database Control is available only with an Oracle license:
t(void 0,'12')
Database Diagnostics Pack
Automatic Workload Repository
ADDM (Automated Database Diagnostic Monitor)
Performance Monitoring (Database and Host)
Event Notifications: Notification Methods, Rules and Schedules
Event history/metric history (Database and Host)
Dynamic metric baselines
Memory performance monitoring
t(void 0,'12')
Database Tuning Pack
SQL Access Advisor
SQL Tuning Advisor
SQL Tuning Sets
Reorganize Objects
t(void 0,'12')
Configuration Management Pack
Database and Host Configuration
Patch Database and View Patch Cache
Patch staging
Clone Database
Clone Oracle Home
Search configuration
Compare configuration

Step6 – Prepare Target environment – Server C with Application related Objects

IMPDP will be used to import Application Related objects into this database.
Before running IMPDP the target database will need to be prepared with the Application Tablespaces and Application Schema’s. This is also a great opportunity to reorg objects if you need to. Scripts to create Application Tablespaces and Schemas are prepared.
This is the most important step in preparing the target environment.

Once the Target environment is prepared – import the dumpfile using the following command.
$ORACLE_HOME/bin/impdp system/manager full=y directory=pump_dir1 dumpfile=pump.dmp logfile=pump_import.log

Before opening the database for public connections
1)Recompile for invalid objects (run utrp.sql)
2)Gather statistics for entire database

There will be a regression tests run at the end of all this to test Application Functionality, long datatypes etc.
Categories: Development

Future Direction - 10g Forms/Reports Developer vs JDeveloper

Vidya Bala - Wed, 2006-12-20 15:15
We are on this new effort to move some legacy rbase programs to Oracle. we are required to evaluate - a)would it be better to go with Oracle 10g forms/reports or with Jdeveloper. The data is going to reside on Oracle database servers. The concern about going with Oracle Forms/Reports was a) will Oracle support it in the future? my instant reaction was ofcourse Oracle will......we have bigger problems if Forms/Reports go away considering that the EBS Suite uses Forms/Reports technology as well.

so the final question while migrating new Apps would it be better to use Oracle Forms/Reports or Jdeveloper????? my 2 cents
1)If the application is database centric without too much business logic involved and if your team has a PL/SQL back ground as opposed to a Java backgrouund then 10g Forms/Reports may be a better bet.
2)If the team is pretty much a J2ee development team then JDeveloper may be the route to go.

I am not too worried about Oracle's strategy to support Forms/Reports (I think they will). The above is just my 2 cents any input from my blog readers will be greatly appreciated.
Categories: Development

Run Flashback commands only from Sql*Plus 10.1.x or newer

Mihajlo Tekic - Fri, 2006-12-15 23:24
getting ORA-08186: invalid timestamp specified each time I tried to run a FVQ.

ORA-08186: invalid timestamp specified
Well ... take a look at the following example

First I wanted to make sure, that the format I use is the correct one.

1* select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') from dual
SQL> /

14-DEC-06 AM

Now, when I tried to run FVQ, I got "ORA-30052: invalid lower limit snapshot expression". That was an expected result, since my lower limit did not belong in (SYSDATE-UNDO_RETENTION, SYSDATE] range. (UNDO_RETENTION parameter was set to 900).
But you can agree with me that Oracle successfully processed timestamp values that I used in this query.

SQL> ed
Wrote file afiedt.buf

1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression

So I modified the lower limit to fit in the right range, and I got ORA-08186: invalid timestamp specified. !?!?!?

SQL> ed
Wrote file afiedt.buf

1 select comm
2 from scott.emp
3 versions between timestamp
4 to_timestamp('14-DEC-06 AM','DD-MON-RR HH.MI.SSXFF AM') and
5 to_timestamp('14-DEC-06 AM','DD-MON-RR HH.MI.SSXFF AM')
6 where
7* empno = 7369
SQL> /
from scott.emp
ERROR at line 2:
ORA-08186: invalid timestamp specified

After some time that I spent trying to resolve this issue (I couldn't dare to open SR about it:-)) I remembered I have had similar problems while trying to test some flashback features (flashback table to before drop) on Sql*Plus 9.2.x while ago….and I was using Sql*Plus 9.2 again.

I tried the same example on Sql*Plus

… and everything worked well.

Are you exporting and importing compressed partitions?

Dong Jiang - Fri, 2006-12-15 11:45

Your luck just ran out.
Oracle imp utility uses convention inserts exclusively and partitions will lose compression after import as the inserts are not direct-path. The shiny 10g datapump has the same limitation. You will have to recompress the partitions later, like
1. Insert(append) into an empty table from the uncompressed partition.
2. Partition exchange
3. Truncate the table.
Then repeat for every uncompressed partition.

Announcement: Oracle Data Mining Consultants Partnership Program

Marcos Campos - Fri, 2006-12-15 10:03
We're starting a program to work with qualified data mining consultants.You and your colleagues are invited to participate in a 2 day hands-on session designed for data mining consultants here in the Oracle Burlington MA office February 7 & 8, 2007. It is also possible to attend remotely via webminar. Space is limited, so please RSVP asap.The Oracle Data Mining Consultants Partnership Program hasMarcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com2
Categories: BI & Warehousing

online reorg options if you are on Standard Edition

Vidya Bala - Wed, 2006-12-13 14:16
A couple of the online reorg options may not be available if your on Std Edition. Quest Central (Space Management) Live Reorg options should help you get past this problem.

Quest Central Space Management have the following Reorg Options
a)Standard Reorg(offline mode no DML activity allowed on Reorg table)
b)Live Reorg (online mode) - has 2 option
1)TLOCK switch (can be used if your on Std edition - a copy table is created and a trigger based approach to get the copy table in sync, when you are ready the TLOCK switch can be performed - during the switch you will need to have a downtime but will be nothing significant)2)Online Switch(This option needs Oracle Partitioning enabled - pretty much you will need enterprise edition for this option)
Categories: Development

From JavaPolis to Nordwijk aan Zee

Clemens Utschig - Wed, 2006-12-13 11:41
After two intense and really cool days at JavaPolis .. and a our slot on standards based applications, that caught a lot of interest - it's now off to an internal architects camp meeting in the Netherlands to discuss on future product strategy.

For the organizers of JavaPolis - it was a great time theree - and 2 thumbs up - for a VERY well organized conference. For me it was something new, speaking in a cinema, and definetely in front of the biggest screen ever (15 * 8 meters)

More pictures can be found here

So I'd recommend for next year to put JavaPolis 2007 into your calendar and join me and 1000 other java geeks :D

Siebel Analytics Install and Siebel Analytics Administration:

Vidya Bala - Tue, 2006-12-12 17:17
In this post I will review Siebel Analytics Administration Tool. But before we begin you will need to Install the following.
Logon to http://edelivery.oracle.com/Ok finding the Siebel Analytics Download can be tricky on edelivery

Product – pick Oracle Business Intelligence and the appropriate Platform

pick the Business Intelligence media pack
If you plan to evaluate on Windows download
B30721-01 Part 1 of 2 and B30721-01 Part 2 of 2
The above 2 parts will your Sibel Analytics Server, Siebel Analytics Web, Siebel Analytics Scheduler, Siebel Analytics Java Host, Siebel Analytics Cluster

Now if your looking to download third party products like Informatica , Actuate etc you will have to download B27745-01 Parts 1 through 4. This post will focus on the Siebel Analytics Server.

Once you have downloaded B30721-01 Part 1 of 2 and B30721-01 Part 2 of 2 , extract the zip files , find the installer and walk through the install. The install is pretty intuitive. If you run into any issues with the install (post comments on the blog and I can help you out with it).

Once you have completed the install , if you are on windows you will see 5 services created Sibel Analytics Server, Siebel Analytics Web, Siebel Analytics Scheduler, Siebel Analytics Java Host, Siebel Analytics Cluster – these are your key components for the Siebel Analytics Server.
Make sure your Siebel Analytics Server and Siebel Analytics Web service is started.

A couple of Siebel Analytics shortcuts will be installed on your desktop.

The first step in using Siebel Analytics to generate Reports is to define the metadata layer. The metadata layer is defined using the Siebel Analytics Administration tool.Click on the Siebel Analytics Administration Tool.

you can see the Administration Tool has 3 layers. The physical layer, Business Model and Mapping Layer and Presentation Layer.

Step1: Physical Layer:

Define your datasources in this layer. Create an ODBC datasource for the source database. For the purpose of this test we will be connecting to the perfstat schema on a DEV1 Instance.
In the physical layer right click and create your database connection

once you create your database folder import your database objects

select the schema you want to import and click import (choose FK constraints if you want to import the objects with FK constraints) – once you have imported the schema you should see it in the dev1 folder

Perfstat schema has been chosen just for illustration purposes , ideally you want your source database to be a warehouse or a mart , in the abscence of one and oltp system can also be your source (note if an oltp db is your source it will call for more work on the business mappings layer)– however in this post I will attempt to design this schema for Reporting Purposes.

Assuming that the crux of your reporting is Reporting on sql statement statististics :
SQL_STMTS_STATS will be our fact table in the Business Mappings Layer.
Some Dimesions around it will be
Instance Details
Execution Plan Cost Details
This is like a 2 dimensional star schema.

Now let us see how the following objects exist in our physical layer and model it in our business layer.
The 4 objects we will be looking at is
Select the above 4 objects in your physical layer - right click and view physical diagram of the selected objects

Now create a new business model folder and drag and drop the 4 objects to the business model layer.

Once you have dropped the objects in the Business layer you can define the relationship between the 4 objects in the Business Layer(select the objects , right click and define the relationships in the Business Diagram area) - this is where a traditional normalized oltp schema in the physical layer will lend into a star or snowflake schema. in the Business layer.

Now let us also look into what attributes we actually need for the Presentation layer and what dimensions we need.

The Business layer is where I start modeling and maping objects with the Business Model in mind.For instance if STATS$DATABASE_INSTANCE is a good candidate for a dimension then right click on the object in the Business Model Layer and say create dimension

Once I have modeled my Business Layer to the way I want it to be , I can drag and drop objects in the Presentation Layer.

so we started with the perfstat schema and this is what we cameup with in the Presentations Layer

Instance Details
- Instance Name
- Database Name
Sql Stats Details
- Sql Statement
- Fetches
- Executions
- Loads
- Parse Calls
- Disk Reads
Sql Plan Cost
- Hash Value
- Cost

All the underlying relationships and hierarchy is masked at this presentation layer. All you see at this presentation layer is key Presentation Elements that a Business user really care about.

The next post will cover how the presentation layer can be used to build Reports using Siebel Analytics Web Console (typically the power users)

For questions – please feel to free to post them in the comments sections.
Make sure to save your work on the Siebel Analytics Administration Tool –a Repository consistency check is done at the time of saving your work – also check in changes will do a Repository Consistency Check.

Categories: Development


Subscribe to Oracle FAQ aggregator