Skip to content

Running OBIEE Oracle By Example Tutorials against a database not called ORCL

23 November 2009, 7:56 pm

I’ve been working with OBIEE for a while now, but I’ve not actually gone through the Oracle By Example tutorials, so I figured it would be a good idea to do that.

I started looking at the first Oracle By Example OBIEE tutorial yesterday and came across an issue with a simple solution to share with you.

The tutorials have a few caveats about what they expect from your environment, when you’re going to run through them – one of them being that you have access to a 10g database. What the prerequisites don’t specifically say is that unless your database is called ORCL, you’ll need to jump through a few more hoops – as I did, with my database called TEST.

I followed the instructions from the tutorial such that I had:

  1. An SH schema in the 10g database, with the standard tables and data Oracle supply.
  2. Created an ODBC Data Source pointing to a database called TEST, checking it functioned correctly.
  3. Restored the presentation catalog and updated the configuration files accordingly.

I then proceeded to login to the BI Dashboards which brought up half the display, but it was full of TNS errors indicating that the connection could not be made between the BI Server and the database where the SH schema resides:

BI Server TNS Errors

After some investigation in the Administration tool, I discovered that the Connection Pool setting was using a Data Source Name called “ORCL” which doesn’t match my TNS/Database called TEST, hence it couldn’t make the connection to the database:

ORCL Connection Pool

Now, the RPD was read only at the time, so I first shut down the services so it could be opened read/write:

BI services down

…logged into the Administration tool using Administrator user (Password Administrator), opened the SH.RPD file read/write and modified the Data Source Name in the Connection Pool from ORCL to TEST, whilst ensuring the password for the SH user matched that of my TEST database:

Change ORCL to TEST

…next I restarted the services:

BI services up

…and then logged on again, to find it all now worked:

TEST all working

Tagged ,
Comments Off

Congratulations to my mate Paul!

11 July 2009, 9:46 am

Just a quick note to say congratulations to Paul Till, a mate of mine, at my current client, who has recently passed his OCM certification. I knew Paul was good, from having worked with him, on a DR implementation / upgrade for a large DW, but I hadn’t realised how good. As certifications go, it’s the daddy and the Oracle one to have.

Tagged , ,
Comments Off

$deleted$ tablespace names bug

11 July 2009, 8:30 am

This one turned out to be a an interesting bug the other day…

I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I checked. I knew the DBA team had been doing some reorganisations the previous weekend, to recover some space, so I wondered if that was connected….it was, and after opening an SR, the DBA, Phil, found an explanation (from Oracle Note: 604648.1) and a resolution.

Reproducing the issue and the way to fix it, is simple, using this script…

DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE new_tbs DATAFILE 'C:\APP\ORACLE\ORADATA\T111\NEW_TBS.DBF'SIZE 100MONLINE;

CREATE TABLESPACE old_tbs DATAFILE 'C:\APP\ORACLE\ORADATA\T111\OLD_TBS.DBF'SIZE 100MONLINE;

SELECT ts#,name FROM sys.ts$ WHERE name LIKE '%TBS';

CREATE TABLE jeff_test(col1 DATE NOT NULL                      ,col2 NUMBER NOT NULL                      ,col3 VARCHAR2(200) NOT NULL                      )TABLESPACE old_tbsPARTITION BY RANGE(col1)SUBPARTITION BY LIST(col2)SUBPARTITION TEMPLATE( SUBPARTITION "S1" VALUES(1),SUBPARTITION "S2" VALUES(2))(PARTITION p1 VALUES LESS THAN(TO_DATE('31-DEC-2009','DD-MON-YYYY')),PARTITION p2 VALUES LESS THAN(TO_DATE('31-DEC-2010','DD-MON-YYYY')))/

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

ALTER TABLE jeff_test MOVE SUBPARTITION p1_s1 TABLESPACE NEW_TBS;ALTER TABLE jeff_test MOVE SUBPARTITION p1_s2 TABLESPACE NEW_TBS;ALTER TABLE jeff_test MOVE SUBPARTITION p2_s1 TABLESPACE NEW_TBS;ALTER TABLE jeff_test MOVE SUBPARTITION p2_s2 TABLESPACE NEW_TBS;

DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;ALTER TABLESPACE new_tbs RENAME TO old_tbs;

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE old_tbs;ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p2 TABLESPACE old_tbs;

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

Which, when run in 11.1.0.6 on Windows 2003 Server 64 bit, gives:

DROP TABLESPACE old_tbs succeeded.CREATE TABLESPACE succeeded.CREATE TABLESPACE succeeded.TS#                    NAME                           ---------------------- ------------------------------ 9                      NEW_TBS                        10                     OLD_TBS                        

2 rows selected

CREATE TABLE succeeded.PARTITION_NAME                 TABLESPACE_NAME                ------------------------------ ------------------------------ P1                             OLD_TBS                        P2                             OLD_TBS                        

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                ------------------------------ ------------------------------ P1_S2                          OLD_TBS                        P1_S1                          OLD_TBS                        P2_S2                          OLD_TBS                        P2_S1                          OLD_TBS                        

4 rows selected

ALTER TABLE jeff_test succeeded.ALTER TABLE jeff_test succeeded.ALTER TABLE jeff_test succeeded.ALTER TABLE jeff_test succeeded.DROP TABLESPACE old_tbs succeeded.ALTER TABLESPACE new_tbs succeeded.PARTITION_NAME                 TABLESPACE_NAME                ------------------------------ ------------------------------ P1                             _$deleted$10$0                 P2                             _$deleted$10$0                 

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                ------------------------------ ------------------------------ P1_S2                          OLD_TBS                        P1_S1                          OLD_TBS                        P2_S2                          OLD_TBS                        P2_S1                          OLD_TBS                        

4 rows selected

ALTER TABLE jeff_test succeeded.ALTER TABLE jeff_test succeeded.PARTITION_NAME                 TABLESPACE_NAME                ------------------------------ ------------------------------ P1                             OLD_TBS                        P2                             OLD_TBS                        

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                ------------------------------ ------------------------------ P1_S2                          OLD_TBS                        P1_S1                          OLD_TBS                        P2_S2                          OLD_TBS                        P2_S1                          OLD_TBS                        

4 rows selected

Notice that the $n in “_$deleted$n$m” is 10, which is the ts# of the OLD_TBS before the rename. The problem revolves around entries in TS$, when you rename tablespaces to names that have previously been used and then dropped, basically because the old entries are not removed from TS$.

Related references:
Bug Numbers:8291493, itself a duplicate of 5769963
Note: 604648.1

According to the SR and bug, it was noticed in 10.2.0.4 and is fixed in 10.2.0.5. We’ve reproduced it in 11.1.0.6 on various ports, (results above) and updated our SR, so I guess the fix might also find it’s way into 11.1.0.7, perhaps.

Tagged , ,
2 Comments

No pruning for MIN/MAX of partition key column

25 June 2009, 8:43 pm

Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.

NOTE – I tried this on 10.2.04 (AIX) and 11.1.0 (Fedora 11) – the example below is from 11.1.0.

I’ll recreate the scenario here:

CREATE TABLESPACE tsp1datafile '/u01/app/oracle/oradata/T111/tsp1.dbf' size 100M autoextend off extent management local  uniform size 1m segment space management auto online/CREATE TABLESPACE tsp2datafile '/u01/app/oracle/oradata/T111/tsp2.dbf' size 100M autoextend off extent management local  uniform size 1m segment space management auto online/

DROP TABLE test PURGE/CREATE TABLE test(col_date_part_key DATE            NOT NULL                 ,col2              VARCHAR2(2000)  NOT NULL                 )PARTITION BY RANGE(col_date_part_key)(PARTITION month_01 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE tsp1,PARTITION month_02 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_03 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_04 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_05 VALUES LESS THAN (TO_DATE('01-JUN-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_06 VALUES LESS THAN (TO_DATE('01-JUL-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_07 VALUES LESS THAN (TO_DATE('01-AUG-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_08 VALUES LESS THAN (TO_DATE('01-SEP-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_09 VALUES LESS THAN (TO_DATE('01-OCT-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_10 VALUES LESS THAN (TO_DATE('01-NOV-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_11 VALUES LESS THAN (TO_DATE('01-DEC-2009','DD-MON-YYYY')) TABLESPACE tsp2,PARTITION month_12 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE tsp2)/REM Insert rows, but only up to 14-JUN-2009INSERT INTO test(col_date_part_key,col2)SELECT TO_DATE('31-DEC-2008','DD-MON-YYYY') + l,      LPAD('X',2000,'X')FROM   (SELECT level l FROM dual CONNECT BY level < 166)/COMMIT/SELECT COUNT(*)FROM   test/SELECT MIN(col_date_part_key) min_date,      MAX(col_date_part_key) max_dateFROM   test/

This runs and gives the following output: 

DROP TABLE test PURGE                                                          *                                                        ERROR at line 1:                                                    ORA-00942: table or view does not exist                             

DROP TABLESPACE tsp1 INCLUDING CONTENTS*                                      ERROR at line 1:                       ORA-00959: tablespace 'TSP1' does not exist

DROP TABLESPACE tsp2 INCLUDING CONTENTS*                                      ERROR at line 1:ORA-00959: tablespace 'TSP2' does not exist

Tablespace created.

Tablespace created.

Table created.

165 rows created.

Commit complete.

  COUNT(*)----------       165

MIN_DATE  MAX_DATE--------- ---------01-JAN-09 14-JUN-09

Now, lets see what the plan looks like from AUTOTRACE when we run the following query to get the maximum value of COL_DATE_PART_KEY:

SQL> SET AUTOTRACE ONSQL> SELECT MAX(col_date_part_key) min_date  2  FROM   test                             3  /                                     

MIN_DATE---------14-JUN-09

Execution Plan----------------------------------------------------------Plan hash value: 784602781                                

---------------------------------------------------------------------------------------------| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |      |     1 |     9 |    99   (0)| 00:00:02 |       |       ||   1 |  SORT AGGREGATE      |      |     1 |     9 |            |          |       |       ||   2 |   PARTITION RANGE ALL|      |   132 |  1188 |    99   (0)| 00:00:02 |     1 |    12 ||   3 |    TABLE ACCESS FULL | TEST |   132 |  1188 |    99   (0)| 00:00:02 |     1 |    12 |---------------------------------------------------------------------------------------------

Note-----   - dynamic sampling used for this statement

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets        320  consistent gets         51  physical reads          0  redo size        527  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

SQL> SET AUTOTRACE OFF

It shows a full scan of all twelve partitions. I figured that the the plan for such a query would show a full table scan, of all partitions for that table – because, in theory, if all but the first partition were empty, then the whole table would have to be scanned to answer the query – and Oracle wouldn’t know at plan creation time, whether the data met this case, so it would have to do the full table scan to ensure the correct result.

What I thought might happen though, is that in executing the query, it would be able to short circuit things, by working through the partitions in order, from latest to earliest, and finding the first, non null, value. Once it found the first, non null, value, it would know not to continue looking in the earlier partitions, since the value of COL_DATE_PART_KEY couldn’t possibly be greater than the non null value already identified.

It doesn’t appear to have this capability, which we can check by taking one of the partitions offline and then rerunning the query, whereupon it complains that not all the data is present…

SQL> ALTER TABLESPACE tsp1 OFFLINE;

Tablespace altered.

SQL> SET AUTOTRACE ONSQL> SELECT MAX(col_date_part_key) min_date  2  FROM   test  3  /SELECT MAX(col_date_part_key) min_date*ERROR at line 1:ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/u01/app/oracle/oradata/T111/tsp1.dbf'

SQL> SET AUTOTRACE OFF

So, even though we know we could actually answer this question accurately, Oracle can’t do it as it wants to scan, unnecessarily, the whole table.

I did find a thread which somebody had asked about this on OTN, but all the responses were about workarounds, rather than explaining why this happens (bug/feature) or how it can be made to work in the way I, or the poster of that thread, think it, perhaps, should.

Can anyone else shed any light on this? If it’s a feature, then it seems like something that could be easily coded more efficiently by Oracle. The same issue would affect both MIN and MAX since both could be
approached in the same manner.

Tagged , , , , , ,
12 Comments

Installing Oracle 11gR1 on a Fedora Core 10 64 bit VM

2 June 2009, 10:26 pm

Just a note, to myself more than anything, about what extra packages are required by a 64 bit installation of Fedora Core 10, when trying to install Oracle 11gR1.

The installation I undertook was on a FC10 64 bit VM running under VMWare Server 2.0 running on top of FC10 64 bit OS.

Tim, as usual, has a lovely guide which told me almost everything I needed to know, however the guide says “If you are performing the 64-bit installation, make sure both the 32-bit and 64-bit libraries are installed.” rather than explicitly stating the packages for a 64 bit install. Until I tried to install Oracle 11gR1, I didn’t know what these were. The Oracle installer for 11g soon told me in the pre install checks it does, so I went about installing the following packages, in order:

glibc-2.9-3.i686.rpm
libaio-0.3.107-4.fc10.i386.rpm
libgcc-4.3.2-7.i386.rpm
glibc-devel-2.9-3.i386.rpm
compat-libstdc++-33-3.2.3-64.x86_64.rpm
compat-libstdc++-33-3.2.3-64.i386.rpm
libstdc++-4.3.2-7.i386.rpm

That got me past the pre install checks of the Oracle installer and on to a successful install.

I’ve added the list to the comments on the guide Tim produced as well.

Tagged , , , ,
Comments Off

Cursor keys not working in Virtual Server 2 VM

22 May 2009, 12:57 pm

Posted as a reminder to myself about how to fix this issue…

I couldn’t get some of the cursor keys to work properly on my virtual machines running under VMWare Virtual Server 2 on Fedora 10 x86_64. Kept giving funny behaviour like bringing up the screen capture applet!

A bit of searching the net came up with this one, which although not referring to Virtual Server 2 specifically, seems to work all the same…

Essentially, adding the line below to the following file fixes the problem

File (create it, if not already present): 

~/.vmware/config

Line: 

xkeymap.nokeycodeMap = true

My thanks to “The Monkey Jungle”!

Tagged , , ,
Comments Off

Disable password ageing on Windows 2008 Server Standard

10 January 2009, 11:53 am

Password ageing in Windows 2008 Server Standard edition (the one I generally use) is set to automatically requre passwords to be changed after 42 days…obviously a Douglas Adams fan responsible for that bit of the codebase!

NOTE – I don’t have access to other version of Windows Server (2003 or 2008) so I can’t speak for them, but I imagine it’s the same on them too.

That’s annoying for home use, where I have tons of VMs for research that I use periodically, so I asked my brother Steve how to stop this happening and he gave me some simple instructions…

First start the Local Security Policy editor by typing secpol.msc in the start/run box..

secpol.msc

and then select Account Policies / Password Policy on the nagivation tree on the left. On the right hand side select Maximum Password Age and set this from the default of “42″ to “0″. You’ll notice it now says “Password will not expire” above the value “0″.

Set Password Ageing off.msc

Seems to have done the trick.

Kinda handy having a brother who’s an MCSE and a VCP too. Useful when I get stuck with OS or VM stuff for my Oracle research!

By the way, if anyone happens to be looking for some skilled contract resource in the Virtualisation field (VMWare, ESX Server etc…) then Steve has just become available…please feel free to contact me, or Steve, via his website

Tagged , ,
Comments Off

VM articles on my new Wiki

28 November 2008, 4:25 pm

I wanted a place to store notes that I could write up from anywhere…but weren’t necessarily relevant to put in a blog, so I now have a Wiki on my website.

Don’t get excited, I’m not planning on hosting a full blown wiki for open editing – it’s just for me.

Amongst the things on there are some short “How to” articles relating to VMWare.

I’m sure I’ll have made mistakes along the way – feel free to point them out via this blog or email me and I’ll sort them out. Comments welcome as well.

Tagged , ,
Comments Off

TPC-H Query 20 and optimizer_dynamic_sampling

30 October 2008, 10:11 pm

I was working with Jason Garforth today on creating a TPC-H benchmark script which we can run on our warehouse to initially get a baseline of performance, and then, from time to time, rerun it to ensure things are still running with a comparable performance level.

This activity was on our new warehouse platform of an IBM Power 6 p570 with 8 dual core 4.7GHz processors, 128GB RAM and a 1.6GB/Sec SAN.

Jason created a script to run the QGEN utility to generate the twenty two queries that make up the TPC-H benchmark and also a “run script” to then run those queries against the target schema I had created using some load scripts I talked about previously.

The whole process seemed to be running smoothly with queries running through in a matter of seconds, until query twenty went off the scale and started taking ages. Excluding the 20th query, everything else went through in about three to four minutes, but query twenty was going on for hours, with no sign of completing.

We grabbed the actual execution plan and noticed that all the tables involved had no stats gathered. In such circumstances, Oracle (10.2.0.4 in this instance) uses dynamic sampling to take a quick sample of the table in order to come up with an optimal plan for each query executed.

The database was running with the default value of 2 for optimizer_dynamic_sampling.

After reading the TPC-H specification, it doesn’t say that stats should or should not be gathered, but obviously in gathering them, there would be a cost to doing so and, depending on the method of gathering and the volume of the database, the cost could be considerable. It would be interesting to hear from someone who actually runs audited TPC-H benchmarks to know whether they gather table stats or whether they use dynamic sampling…

We decided we would gather the stats, just to see if the plan changed and the query executed any faster…it did, on both counts, with the query finishing very quickly, inline with the other twenty one queries in the suite.

So, our options then appeared to include, amongst other things:

  1. Gather the table stats. We’d proved this worked.
  2. Change the optimizer_dynamic_sampling level to a higher value and see if it made a difference.
  3. Manually, work out why the plan for the query was wrong, by analysis of the individual plan steps in further detail and then use hints or profiles to force the optimizer to “do the right thing”.

We decided to read a Full Disclosure report of a TPC-H benchmark for a similar system to see what they did. The FDR included a full listing of the init.ora of the database in that test. The listing showed that the system in question had set optimizer_dyamic_sampling to 3 instead of the default 2…we decided to try that approach and it worked perfectly.

In the end, given we’re not producing actual audited benchmarks then we’re free to wait for the gathering of optimizer stats, so we’ll go with that method, but it was interesting to see that option 2 above worked as well and illustrates the point that there is a lot of useful information to be gleaned from reading the FDRs of audited benchmarks – whilst, of course, being careful to read them with a pinch of salt, since they are not trying to run your system.

Another thing of interest was that in order to get the DBGEN utility to work on AIX 6.1 using the gcc compiler, we had to set an environment variable as follows otherwise we got an error when running DBGEN (also applies to QGEN too):

Set this:

export LDR_CNTRL=MAXDATA=0×80000000@LARGE_PAGE_DATA=Y

otherwise you may get this:

exec(): 0509-036 Cannot load program dbgen because of the following errors:
0509-026 System error: There is not enough memory available now.

Tagged , , ,
1 Comment

Fedora 9 on Hyper V on Windows 2008 Server x64 – Firefox IPv6 DNS issue

30 August 2008, 10:25 pm

I’ve been playing with Hyper V this week and came across an issue with Firefox, whereby I thought that the networking setup for Fedora 9 on a Hyper V virtual machine wasn’t working.

The environment is Windows 2008 Server on x64 with the RTM release of Hyper V. I then created a VM and installed Fedora 9 x64 from a DVD ISO – it took a while as I started with a multiple CPU, standard network adapter approach and it kept crashing during the install; when I changed to single CPU and “Legacy” network adapter it installed cleanly – given that Fedora isn’t a “supported” Hyper V OS I can’t really complain.

The problem I then came to was that it appeared that DNS wasn’t working, i.e. in Firefox, if I stuck in a URL, it came back with an error rather than showing the page. I then proceeded to go off on a wild goose chase checking all the Fedora networking setup and reading about Hyper V and it’s “synthetic” network adapter approach, but still couldn’t solve it. Everything seemed to be installed and configured exactly as everybody who had written about it had said…so it should be working right?

It was…but the problem is that Firefox was trying to resolve the name of the URL using IPv6 rather than IPv4…and my network was configured without an IPv6 DNS – I don’t need IPv6 and I’m perfectly happy with IPv4 so that’s what I use. I only discovered this by chance as I wondered whether it might be a Firefox issue (everything else ruled out kinda thinking), so I used the Konqueror web browser to see if it had the same issue and hey presto it was working fine whilst Firefox wasn’t.

How to fix Firefox? Well, basically as indicated in this post on the OpenSUSE website – essentially, you go to the URL “about:config” in Firefox, filter for “network.disable.IPv6″ and set it to TRUE instead of FALSE (double click it to change the value). Then it all works fine.

Tagged 
Comments Off
« Older Entries