Created On:  12July2012

While the official position on how to use SilkTest WorkBench with an oracle database backend is documented in the product Installation and configuration guide which is shipped with the product; if you do not have the product installed or need a diferent version of the documentation, you can also get the documentation from the following link:

http://supportline.microfocus.com/productdoc.aspx

The procedure to create an oracle DB is quite complex and typically needs to be done by the company DBA/your oracle DBA; additionally, as different companies have their own security requirements to meet, DBA's do not always follow our method.


If you already do have an oracle instance running somewhere in a non-production environment and would like to quickly create a STW backend database for testing purposes without the assistance of your DBA, here I will show you how you could quickly set one up.

Disclaimer/WARNING: I will not be held responsible from any issues or data loss arising from you following the steps in this document. Do NOT attempt this in a production environment. And allways take backups of your critical data, in case something goes wrong and you need to restore your environments/systems at a later date.

Resolution:

Here I will show you how to create a new oracle database to be used by SilkTest Workbench:

Pre-requirements:
- you need to have a supported oracle instance already installed, please refer to the STW product documentation for more details about what oracle database versions, the STW version you using supports.
- You need to know the instance name, you can check what this name is via the windows services, for example for the purpose here my instance is called QACUTFDB, when i look at my windows services I have a service called "OracleServiceQACUTFDB", please note the last part of the service name here highlighted in Red "OracleServiceQACUTFDB"
- You will need to know the location of the data files, in my instance the folder location is: C:\oracle\product\10.2.0\oradata\QACUTFDB\
- You will need to know the password for the SYS account (other sys accounts may also be used but have not been tested or may not be enable in your instance).
- (Optional) You should have some basic batch file creation experience in order to understand what the bat files command are doing.

What we are going to do is create 2 batch files to achieve our goal:


1 bat file - Copy the content below and create a batch which WE MUST ONLY RUN ONCE
------------- beginning -------------
rem pre-requirements --> that the ORACLE DB supports UTF8 character set
rem pre-requirements --> If this is not the case then use database configuration assistance to create a new DB
rem ---- pre-define variables ----
SET ORASYSUSER=sys
SET ORASYSPASS=microfocus
SET ORASERVICE=QACUTFDB
SET ORATABLESPACE=STW
SET ORADATAFILE='C:\oracle\product\10.2.0\oradata\QACUTFDB/%ORATABLESPACE%.dbf'
SET ORASIZE=50M

rem ---- write SQL script to file called tablespace.txt ----
echo CREATE TABLESPACE %ORATABLESPACE% > tablespace.txt
echo DATAFILE %ORADATAFILE% >> tablespace.txt
echo SIZE %ORASIZE% >> tablespace.txt
echo AUTOEXTEND ON NEXT 10M MAXSIZE unlimited; >> tablespace.txt
echo QUIT; >> tablespace.txt

rem ---- execute SQL script ----
sqlplus.exe "
%ORASYSUSER%/%ORASYSPASS%@%ORASERVICE% AS SYSDBA" @tablespace.txt
---------------- end ----------------

I hope the code in bat file above is enought self explanatory.

First, Ensure that you update/replace the pre-defined variables, with the correct values that match the details in your ORACLE environment: ORASYSUSER, ORASYSPASS, ORASERVICE, ORATABLESPACE & ORADATAFILE (note variable ORASIZE value does not need to be changed), save the .bat file changes, and then run this .bat file from a CMD prompt (ensure you start the CMD prompt with Run As Administrator permissions) in the Oracle environment.

Verify If the batch file executed successfully and verify that the data file now exists.
If see some oracle error or the data file was not created properly then go back fix the problem and try again.
Your DBA should be able to assist you with any additional oracle questions you may have,
or you may be able to find more information about any oracle errors out there on the web by searching for the particular error in www.google.com

 

2 bat file - Copy the content below and create a 2nd batch as follow 
------------- beginning -------------
rem ---- pre-define variables ----
SET ORASYSUSER=sys
SET ORASYSPASS=microfocus
SET ORANEWUSER=STW
SET ORANEWPASS=microfocus
SET ORASERVICE=QACUTFDB
SET ORATABLESPACE=STW
SET ORATEMPDB=temp

rem ---- write SQL script to file called db.txt ----
echo CREATE USER %ORANEWUSER% > db.txt
echo        IDENTIFIED BY %ORANEWPASS% >> db.txt
echo        DEFAULT TABLESPACE %ORATABLESPACE% >> db.txt
echo        TEMPORARY TABLESPACE %ORATEMPDB%; >> db.txt
echo GRANT CONNECT TO %ORANEWUSER%; >> db.txt
echo GRANT DBA TO %ORANEWUSER%; >> db.txt
echo GRANT RESOURCE TO %ORANEWUSER%; >> db.txt
echo GRANT CREATE ANY TABLE TO %ORANEWUSER%; >> db.txt
echo GRANT UNLIMITED TABLESPACE TO %ORANEWUSER%; >> db.txt
echo ALTER USER %ORANEWUSER% QUOTA unlimited ON %ORATABLESPACE%; >> db.txt
echo QUIT; >> db.txt

rem ---- execute SQL script ----
sqlplus.exe "
%ORASYSUSER%/%ORASYSPASS%@%ORASERVICE% AS SYSDBA" @db.txt

rem ---- execute other SQL scripts to populate the database with the tables ----
rem ---- either create the DB from the maintenance tool or run the line below ----
rem sqlplus.exe
%ORANEWUSER%/%ORANEWPASS%@%ORASERVICE% @"ORACLE.SQL"
---------------- end ----------------

Again same concept applies here and I hope the bat file commands are enought self explanatory.

First, again here you must ensure update/replace the pre-defined variables, with the correct values that match the details in your ORACLE environment: ORASYSUSER, ORASYSPASS, ORANEWUSER, ORANEWPASS, ORASERVICE & ORATABLESPACE (note the variable ORATEMPDB value does not need to be changed), save the .bat file changes, and then run this .bat file from a CMD prompt (ensure you start the CMD prompt with Run As Administrator permissions) in the Oracle environment.

Note, how the very last line/cmd of the batch file is commented out, so is not executed, instead we will manually populate the database tables by using the STW database maintenance tool.

After you run the .bat file, please verify If the batch file executed successfully.

- If you got any oracle errors that may have stopped some of the bat command from completing successfully,
then you may need to go back and fix then or start over.
Your DBA should be able to assist you with any additional oracle questions you may have.
Further below, I will added another .bat file that will allow you to also remove/wipe the database created above, including removing user and remove data file from the instance.

- Or If everything in the .bat file run as expected then
- (create DSN) you now should be able to create a new oracle system DSN (if you running a 64-bit OS ensure you use the 32-bit ODBC tool - "C:\Windows\SysWOW64\odbcad32.exe"), when creating the system DSN please ensure you use the values from the ORANEWUSER, ORANEWPASS & ORASERVICE variables, ensure you use the oracle driver (not the microsoft driver) and finally verify that the DSN tests successfully.
- (Populate Tables) Then you need to start the STW database maintenance utility (click Start/All Programs/Silk/Silktest/Administration Tools/Database maintenance). At this point please remember that the DB is actually still blank/empty(no tables exist yet) so then you will need to populate the database with the product tables as follow (in the Database maintenance utility, Click File menu, select New Database, select Oracle... browse and select the system DSN which you created in the previous step, enter the user ID (this is the value defined in the ORANEWUSER variable in bat file #2), enter the Password (this is the value defined in the ORANEWPASS variable in bat file #2) and finally click the Create button.
- (Connect STW to the new DB ) Finally you should be able to connect STW to the new oracle database, by starting STW (Click Start/All Programs/Silk/SilkTest/locate and start SilkTest Workbench), do not login, instead click the configure button, select radio button for Oracle from the data source type, select the system DSN from the data source, enter the Schema (user ID and Schema in oracle are always the same) (this is the value defined in the ORANEWUSER variable in bat file #2), Enter the User Name (this is the value defined in the ORANEWUSER variable in bat file #2) and finally enter the Password (this is the value defined in the ORANEWPASS variable in bat file #2), Click the Validate and then Apply if tests sucessfully. Close configuration window. Ensure the Database is now set to the new DB and then login with the default credentials Admin/admin.

If everything went as described, you now should be able to use SilkTest Workbench with your oracle database backend.

 


* WARNING - the following bat file below will wipe/delete/remove the data created above and we cannot be held responsible for the consequences of any issues or data loss arising from its use or misuse.

How to wipe/delete/remove the oracle database (not the instance) and all the data created in the in the previous steps:

3 bat file - Copy the content of the text below into a new bat file
------------- beginning -------------
rem ---- pre-define variables ----
SET ORASYSUSER=_sys
SET ORASYSPASS=_microfocus
SET ORASERVICE=_QACUTFDB
SET ORATABLESPACE=_STW
SET ORADATAFILE="_C:\oracle\product\10.2.0\oradata\QACUTFDB\%ORATABLESPACE%.dbf"
SET ORANEWUSER=_STW

rem ---- write SQL script to file called dropdb.txt ----
echo DROP USER %ORANEWUSER% CASCADE; > dropdb.txt
echo DROP TABLESPACE %ORATABLESPACE%; >> dropdb.txt
echo QUIT; >> dropdb.txt

rem ---- execute SQL script ----
sqlplus.exe "
%ORASYSUSER%/%ORASYSPASS%@%ORASERVICE% AS SYSDBA" @dropdb.txt

rem ---- delete datafile ----
rem ---- sometimes the file is locked after use and does not delete automatically ----
rem ---- in such cases you may need to stop the oracle service related services and manually delete the file ----
del %ORADATAFILE%
---------------- end ----------------

First, again here you must ensure that you update/replace the pre-defined variables, with the correct values that match the details in your ORACLE environment: ORASYSUSER, ORASYSPASS, ORASERVICE, ORATABLESPACE, ORADATAFILE, etc, save the .bat file changes, and then run this .bat file from a CMD prompt (ensure you start the CMD prompt with Run As Administrator permissions) in the Oracle environment.

The purpose of providing this dangerous batch file here, is in case you need to start over and need to drop the tables, the user and datafile created in steps above.