Everything Oracle

Can’t install BI 10.1.2.0.2 sample files due to invalid password

Q: I’m trying to install “bi_10_1_2_0_2_samples”. I followed the pre-installation checklist in “installing_common_schema.html”. When I run “install_rel.bat” I get an “invalid username/password” when it’s upgrading the SH schema tables. But the script doesn’t take any parameters and the earlier part of the script has already logged successfully onto the database several times without an error?

You haven’t done anything wrong, but Oracle has, once again, failed to adequately document its software. In the checklist Oracle states:

The SH common schema must have been installed and the SH user account must be unlocked and active (not expired).

The vital piece of information lacking from this prerequisite is that the password for account SH must be SH. Now, of course, when a DBA installs the Oracle database he will usually unlock various accounts and set suitable passwords for them. Hence, as night follows day, some users trying to install the sample data for the tutorial will find that the password of SH is not SH.

The reason why the error is particularly confusing is that for the initial set of SQL scripts run from batch file “install_rel.bat” Oracle uses an operating system logon of “/”, but when it gets to the SH schema section (script “dbdisco.bat” in the “disco” subdirectory”) it has hardcoded the logon as “SH/SH”. This explains why the first set of SQL logons work, but then the logons suddenly fail.

So the fix is to set the password of SH to SH, or else to find an alternative software vendor possessed of a greater fondness for documentation and testing than the incumbent. As you’ve discovered, it generally takes far longer to install an Oracle tutorial than to work through it!

BI 10.1.2.0.2 sample file installation fails due to error in log file

Q: When I was installing the OBE samples for BI Discoverer the script crashed. I fixed the error and ran the script again. Then I got an error message saying that there are errors in the log file but “install.log” just has “exit 0” in it?

The problem here is that the “install_rel.bat” script is not re-entrant. If you run it a second time you’ll find that it complains that it can’t find “disco\dropsh.log”. Create a dummy log file for it to delete, try again, and you’ll get the message

Errors in the log file, please review file INSTALL.LOG

As you’ve found out, there’s nothing informative in “install.log”. But if you go into the “disco” subdirectory you’ll find a “profits.log” file which will have

create table profits_tab as select * from profits

ORA-00955: name is already used by an existing object

Oracle assumed you’d never have cause to run the file twice. Just add

spool dropsh.log

drop table profits_tab;

spool off

at the beginning of file “create_profits_tab.sql” in the “disco” directory.

EUL tables are inaccessible during installation of 10.1.2.0.2 tutorial samples

Q: I’m trying to install the BI 10.1.2.0.2 samples with 10.1.2.1 of the BI Tools and an 11g database on Windows. When I run “install_rel.bat” to install the Discoverer workbooks I get an error saying that the EUL tables are inaccessible and “can’t grant quota on tablespace”?

This is a bug. Script “install_rel.bat”, first performs various checks, one being that user “bi_user” does not exist, and it then calls script “mtdisco.bat” in the “disco” subdirectory. The first thing “mtdisco.bat” does, after setting the arguments, is to issue the command:

java

–jar %BI_HOME%\bin\eulbuilder.jar

–create_eul

–connect system/%SYSTEM_PASSWORD%@%TNSNAMES%

–user bi_user

–password bi_user

–temporary_tablespace %TEMPORARY_TABLESPACE%

–default_tablespace %DEFAULT_TABLESPACE%

–log create_eul.log

It is this command that fails. In log file “create_eul.log” you’ll find the actual parameters used together with the message:

Default or specified schema containing EUL tables is inaccessible

Database Error – ORA-30041: Cannot grant quota on the tablespace

The purpose of this command is to create an Oracle user and then create a Discoverer EUL for that user. The command creates the user, but then if fails, so that the Discover EUL is never created. If you run the command separately, passing in the appropriate parameters, you’ll find it returns an “OCIError” and the database error corresponding to the failed grant – the command seems to be trying to grant a quota on the temporary tablespace.

The workaround is to first create user “bi_user” and the EUL manually and then call a modified version of the script with the “create eul” call to java commented out. However, as the script “install_rel.bat” checks for the existence of user “bi_user” and will abort if it finds it present, this check within “install_rel.bat” (at label “bi_userexists”) must also be commented out. When the script is run the three summary refreshes at the end of “mtdisco.bat” will fail, so the refreshes must be done manually within Discoverer Administrator. However, by now, the pair of scripts is doing very little for you – running a grant script and doing an import – so it is faster to ignore the scripts and perform the equivalent tasks directly using SQL*Plus and Discoverer Administrator as described below:

Logon onto SQL*Plus as SYSTEM and issue the following commands to create user “bi_user” and grant it the necessary privileges:

      create user bi_user identified by bi_user;
      grant connect, resource to bi_user;
      @<sample home>\discoverer\disco\grant_bi_user_privs.sql

Log into Discoverer Administrator as username “bi_user”, password “bi_user” and the database connect identifier found in the “tnsnames.ora” file (in directory “\network\admin”).

Create an EUL by selecting “Yes” when asked to create an EUL, and by then clicking on “Create an EUL” in the “EUL Manager”. In the next screen select “Select an existing user”, clear the “Grant access to PUBLIC” checkbox, and press the “Select” button. In the next screen click on the “Go” button, select “bi_user” from the pop-up list, and click on “OK”. Finally click on “Finish” to create the EUL. Click “OK” when the EUL has been created, followed by “No” when asked if you wish to install the sample data (the sample data needed for this tutorial is imported from a file in the next step). Click on “Close”, and then click “Cancel” on the “Load Wizard”. Close the “Administrative Tasklist” pop-up that appears

Import the sample data by selecting “File” and then “Import” from the main menu. Click on “Add File”. Browse to select file “<sample home>\discoverer\disco\bi_user.eex”. Click “Open” and then “Next”. Click on “Rename the existing object”, “Next” and then on “Start”. Click on “Finish”.

To refresh the summaries, click on the “Summaries” tab, expand the “Sales History” node, and select all four summaries. Then right-click on any summary and select “Refresh” from the pop-up. Finally click on “OK”.

Sqlplus not recognised when trying to install 10.1.2.0.2 tutorial samples

Q: When running “install_rel.bat” for the 10.1.2.0.2 tutorial samples I get an error saying that sqlplus is not recognized as an internal or external command (I’ve installed the 11g database in one Oracle home and the BI tools in another on the same Windows machine)?

Unfortunately, the 10.1.2.0.2 tutorial samples installation script will not work for the simplest BI configuration – one that you, and many other users, will have setup. In the case of the 11g database with a standard install you’ll have SQL*Plus installed in the “bin” directory of the Oracle database home (and you’ll also have no “tnsnames.ora” set up in “\network\admin” – more on this later). When you install the BI tools, in a separate Oracle home as required, SQL*Plus will not be installed in the corresponding “bin” directory.

The tutorial samples installation process is a little convoluted. Oracle asks you to set your ORACLE_HOME environment variable to the Oracle BI home directory. When you run script “install_rel.bat” it sets variable BI_HOME to the current ORACLE_HOME. Now within scripts “install_rel.bat” and “mtdisco.bat” (called from “install_rel.bat”) BI_HOME is used in the java command calls to identify the source of the “eulbuilder.jar” file. This will work since BI_HOME has been set to point to the Oracle BI home. In SQL*Plus commands the directory containing SQL*Plus is identified sometimes by ORACLE_HOME and sometimes by BI_HOME. None of these SQL*Plus commands will work since both variables point to the BI bin directory that does not have SQL*Plus installed.

Now you could rework the two scripts so that all environment variables that reference SQL*Plus point to the bin directory in the Oracle database home, and not to the bin directory in the Oracle BI home. You’d also have to remove the references to “%TNSNAMES%” in the calls to SQL*Plus since there will be no “tnsnames.ora” in the network admin directory of the Oracle database home by default (the references to “tnsnames” in the connect string in the java commands are required as otherwise the java-based, BI-homed software could not find the database). If you want to workaround the problem in this way make copies of “install_rel.bat” and “mtdisco.bat”, say “install_rel_new.bat” and “mtdisco_new.bat”. In “install_rel.bat”

Replace all references to %ORACLE_HOME% by %DB_HOME%;

Comment out the tests at label “:validateargs”;

After the “:setcommonvars” label hardcode all the values into the variables, and add a separate assignment for variable DB_HOME;

Remove @%TNSNAMES% from the SQL*Plus commands; and

Change the call to “mtdisco.bat” to “mtdisco_new.bat”.

In “mtdisco_new.bat”

Replace all references to %ORACLE_HOME% by %DB_HOME%;

Replace the reference to %BI_HOME% in the SQL*Plus command by %DB_HOME%;

Comment out the tests at label “:main”;

After the “:setargs” label hardcode all the values into the variables; and

Remove @%TNSNAMES% from the SQL*Plus command.

These changes will workaround the problem that you’re having at present but the modified scripts will still fail due to a bug that prevents an EUL from being created. You’ll find the workaround for this bug here (as this workaround ignores the scripts and uses SQL*Plus and Discoverer Administrator to create the EUL and to import the sample data directly, it will prove far faster than trying to rework the installation scripts).

Everything Oracle

Home

Everything Oracle

FAQ	OBE	Tutorials

A collection of bugs, boojums and Oracle oopsies!