Oracle DBA Books

My store site:

www.dbabookstore.com

Oracle Business Intelligence (OBI) is Oracle Corporation’s flagship business intelligence product. It incorporates state of the art software for reporting and for defining business metadata.

OBI includes an application server known as OC4J, which handles the web based requests to the OBI server. OC4J is installed as a program which starts upon logon to the Windows server. This configuration requires that someone is always logged into the server with the OC4J running in their session, or the OBI front end will not run. While this does work, it does impose a restriction on the server which is not standard for programs of this kind. A more standard and potentially more robust solution is to configure OC4J to run as a Windows service, so that it starts when the server is booted up regardless of whether anyone actually logs onto the server.



Step 1

First, you have to download Open Source software that will set up the windows service
1. Create a directory for the software, such as C:\JavaService
2. Go to http://javaservice.objectweb.org
3. Download the javaservice program. On my computer the folder C:/Temp is where downloaded files are placed.
a. Select javaservice:
b. Then, select JavaService-2.0.10.zip
c. You have to fill in a form, and agree to the Open Source license:
d. Select the Download site:
e. Click OK to download
f. Copy the zip file to c:\JavaService






Step 2

Use your favorite zip utility to unzip the file, or use the Extract File selection from the menu when you right click on the file:

1. Press Next, and press Yes to overwrite any files that may be flagged as duplicates.
2. Press Finish and you will see the contents of your new folder. You are ready to proceed to the next step.



Step 3

In order to complete the installation, follow the following steps. Two assumptions are made in this script: you have installed OBIEE in the default c:\OracleBI directory, and you have installed Java in C:\Java. For the script to run successfully, you need to verify on your system where these components have been installed.

1. Open a DOS command window
2. Change directory to C:\JavaService\JavaService-2.0.10
3. Run the following command, all in one line. Be sure to edit this to reflect the actual location of the jvm.dll. This is highlighted in bold blue below:
javaservice.exe -install Oracle-BI-OC4J-Server C:\Program Files\Java\jre1.5.0_11\bin\client\jvm.dll -XX:MaxPermSize=128m -Xmx512m -Djava.class.path=C:\OracleBI\oc4j_bi\j2ee\home\oc4j.jar -start oracle.oc4j.loader.boot.BootStrap -description Oracle0-BI-Oc4J-Service

Step 4

To verify that the service is running, go to Control Panel/Administrative Tools and click on Services. By default, the OC4J service is set up to start Automatically, and is not started at this point.



Click on your new Oracle-BI-OC4J-Server service to bring up the Service Properties:

Start your service, and you are good to go.


Caveat

This process has been implemented on many servers in many companies. However, it is important to note that as of the writing of this article, Oracle Corp. does not officially certify use of this process. So, use this at your own risk.



Conclusion

We have seen that in four very simple steps, you can create a Windows service to start the OC4J web server on your OBIEE installation. This will make your life, and the lives of your OBIEE community, easier and more productive.

Who's blocking now?

It's always good to remember that every screen in Oracle Enterprise Manager, or Quest Spotlight, or other GUI tools, is really the result of a SQL select statement. There is power in knowing what the tools are actually doing, and what data dictionary views are really being accessed. Here's a little one that gives the DBA the information on who's session is blocking someone elses:

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

BLOCKING_STATUS
-----------------------------------------
gene@myserver ( SID=33 ) is blocking joe@otherserver ( SID=55 )

Once you have that information, you can investigate further, and take action.
The action can be anything from going over to someone's cubicle and having them press the SAVE button, to killing a session.

Trade Your F-150 Discoverer Pick up for an OBIEE Lexus

It's always true - I'm never sure how many people will be there when I give a presentation. Will it be 4 or 40? And then when I have a topic that's a bit narrow, I'm really surprised when I get 30 people. But that's how many people wanted to find out about trading in a F150 Discoverer Pick-up truck on a shiny new OBIEE Lexus this past May at Collaborate 2009 .All the people in the room were Discoverer users or admins. None had OBIEE. That really shows the push throughout various industries for companies to work towards better, faster, more robust business intelligence.

My talk stepped through how to extract Discoverer metadata, and migrate that to OBIEE repository files. Then we saw how to verify the success of the migration by connecting back to the original database and viewing some results.

What was interesting was the depth of the questions the attendees were asking:
Will my current security model be used by OBIEE?
When we migrate to OBIEE, can we turn off the old Discoverer database with metadata?
Can we run OBIEE in a Linux environment?

So the basic steps to get your Discoverer EUL metata into OBIEE? (See the answer to this one below)- Extract the Discoverer Metadata- Use the OBIEE migration tool- MigrateEUL.exe- Create a new RPD file- OBIEE uses repository Data file- Verify connectivity in new RPD file- View the new OBIEE reports
My white paper goes into more detail, but as you can see the basic steps are few.
BTW - I drive an old Hyundai!

RMAN Convert database

One of the nifty tools in RMAN is the Convert Database which lets you copy a database from one operating system to another. This allows you to migrate a database in very few steps from one OS to another, say from Solaris to AIX. No export/import, transportable tablespaces, or data base links. Just a simple rman command and start up the database in your new environment.
Here's how you do it:

• First you have to verify that the source and destination platforms share the same endian format. (endian format relates to which bytes are most significant in multi-byte data-types). You can do this by running this select statement:

SQL> select PLATFORM_NAME, ENDIAN_FORMAT
2 from V$TRANSPORTABLE_PLATFORM;

• Next, run DBMS_TDB to verify that there are no underlying problems that would stop the conversion:

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 external boolean;
3 begin
4 external := dbms_tdb.check_external;
5 end;
6 /
The following directories exist in the database:
SYS.DATA_PUMP_DIR

PL/SQL procedure successfully completed.

If you see no messages, you are good to go. Otherwise, take care of any warnings first.



• Now, start RMAN and run the convert. This sample assumes we are moving from a Windows ASM on to a Linux environment:


>rman target / nocatalog

RMAN> CONVERT DATABASE NEW DATABASE 'LinDB10g'
2> TRANSPORT SCRIPT 'D:\oracle\oradata\dbTransport\transport.sql'
3> TO PLATFORM 'Linux IA (32-bit)'
4> DB_FILE_NAME_CONVERT '+DG_DATA/ora10g/datafile' 'D:\oracle\oradata\dbTransport';

The RMAN Convert creates the following:
a) A complete copy of the datafiles of the database, ready to be transported
b) A PFILE for use with the new database on the destination platform, containing settings used in the PFILE/SPFILE from the source database.
c) A transport script, which contains SQL statements used to create the new database on the destination platform.

• Copy the files to the new system
• Edit the init.ora file, and the transport script, to reflect your new environment
• On your new server, make sure your ORACLE_HOME and ORACLE_SID are set to your new database
• run sqlplus and do the conversion:

[oracle@test-br LinDB10g]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 3 01:55:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @TRANSPORT.SQL

You will get messages as the process procedes. If it is a large database, the process may take a couple of hours. When the transport script finishes, the creation of the new database is complete.

• I always add a spool command to the transport script. It's not in there by default, and it's always a good idea to have a log file of a process of this magnitude.
• Be sure to do a basic health check and make sure things are OK.
• Change any directories, external tables, bfiles, etc. to match to your new server

But - that is it. Fairly painless way to copy a database to a new operating system.