You are all aware that Workspce streaming supports PostgreSQL (default bundled), MS-SQL and in addition Oracle as well. As of date (when the article was written) workspace streaming supports 2 versions of Oracle database namely, 10g and 11g. This article outlines some of the configurations on Oracle side and what needs to happen at the time of install / configuration of workspace streaming. In several cases, due to security and role constraints team involved with server install do not have access to the database. Due to this reason, this article will focus on dealing with these two separately then bring together at the end of the configuration.
Oracle configuration
- While configuring Oracle for Workspace Streaming ,please make sure the following character set is selected.
-
-
- Please ensure the "login user" (defined as USERNAME in example) going to be used for database connection on Workspace Streaming configuration have the following access privileges .
1. The "USERNAME" should be created with default table space users, temporary table space temp, profile default and account unlock options
2. The "USERNAME" specified should have been assigned to Default ROLE "ALL"
3.The user specified should have been given
SELECT_CATALOG_ROLE, CREATE VIEW,
CREATE SESSION, ALTER ANY TABLE, CREATE ANY INDEX, CREATE ANY TABLE,
CREATE PROCEDURE, CREATE ANY SEQUENCE with grant option.
4. The USERNAME" should also have specified "QUOTA UNLIMITED ON USERS"
Ex. user creation command:
CREATE USER [USERNAME]
IDENTIFIED BY "[PASSWORD]"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT SELECT_CATALOG_ROLE TO [USERNAME] WITH ADMIN OPTION;
ALTER USER [USERNAME] DEFAULT ROLE ALL ;
GRANT CREATE VIEW TO [USERNAME] WITH ADMIN OPTION;
GRANT CREATE SESSION TO [USERNAME] WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO [USERNAME] WITH ADMIN OPTION;
GRANT CREATE ANY INDEX TO [USERNAME] WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO [USERNAME] WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO [USERNAME] WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO [USERNAME] WITH ADMIN OPTION;
ALTER USER [USERNAME]
QUOTA UNLIMITED ON USERS;
5. Use this "login user" credential in "connection parameter" property in Workspace Streaming configuration
Configuring Workspace Streaming schema
If you are configuring from the Oracle Server itself or some other machine, then you have to perform the below steps, which is exactly what the “CreateDatabaseSchema.cmd” does. To perform these steps, you will requires the contents of the scripts folder “[INSTALLDIR]\streamletEngine\db\scripts\oracle”. In the latter case, here is the order in which you will have to execute the scripts:
- create_appstream-db.sql
- create_appstream-user.sql
- create_appstream-schema.sql
- id-table-schema.sql
- create_appstream-schema-idtable-init.sql
- init_appstream_schema.sql
Oracle RAC configuration [Advanced]
Some cases, Oracle for Workspace Streaming can be configured in RAC (clustered Oracle installation with automatic failover capability) configuration. Refer to Oracle RAC overview at http://www.oracle.com/technetwork/database/clustering/overview/index.html
With Workspace Streaming, here are 2 ways of connecting to this server configuration
- Using thin JDBC driver
- Using thick (or OCI) JDBC driver
If the latter (thick driver) is used, then you can perform the installation with the normal approach of connecting to any database (i.e. provide all the necessary credentials, host info, SID, etc in the server install) and we are good to go. On the other hand, if they are using the former (thin driver, which is the default we test and recommend), the format of the connection URL is different, in other words it requires a fully qualified name of the connection just like what is defined in the tnsnames.ora file. If you are interested here is the format for this connection (this goes in da.conf – look for jdbc:oracle:thin and replace the whole line with the below, starting from jdbc:oracle:thin):
jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)
(HOST=xxxxx)(PORT=1526))(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1526)))(CONNECT_DATA=
(SERVICE_NAME=somesid)))
Workspace streaming install
Since we are dealing with these two components separately, when installing the streaming backend server, select 3rd option as shown below during database and server selection screen. This option means, we are only installing the streaming piece without doing anything to the database side.
Once you finish the install with this option selected, you will see a popup that displays information on configuring the database.
This content in this pop-up and “CreateDatabaseSchema.cmd” file referred to in this readme are located under [INSTALLDIR]\streamletengine\bin.
Workspace Streaming configuration
This portion is done manually since install and configurations are done separately. All these commands are done from command line.
1. Following changes has to be done in [INSTALLDIR]\streamletengine\da\conf\da.conf
a. Comment configuration properties in the "postgresql" section if you plan to use external database
b. Uncomment configuration properties of the database which you plan to use
c. Find and replace value of 'appstream.database.host' property with 'Host name or Ip' of the machine in which database is installed.
d. Replace value of 'appstream.database.port' property with 'Port No' in which database is listening
e. Replace value of 'appstream.database.name' property with [Instance Name] you are going to use
[Note: For RAC configuration refer to "Oracle RAC configuration" section above]
2. Get Ojdbc6.jar from oracle installed machine and copy to the following locations on backend server
[Note: For RAC configuration you'll be copying a jar file that is RAC aware]
- '[INSTALLDIR]/agent/lib',
- '[INSTALLDIR]/common/apache-tomcat/shared/lib' and also to
- '[INSTALLDIR]/common/jboss-4.2.2/server/appstream/deploy/ste/ste.jar'
3. Execute 'CreateDatabaseSchema.cmd' from '[INSTALLDIR]/streamletEngine/bin' directory
Note: This step is required if and only if the schema was NOT run from the Oracle server (or elsewhere)
CreateDatabaseSchema.cmd [admin user] [admin password] [instance name] [db option] [asuser password]
[admin user] user with privilege to create user, grant access, create schema, etc
[admin password] password for the [admin user]
[instance name] database instance referred to in tnsnames.ora file
[db option] database server type, in this case "oracle"
[asuser password] password assigned for "asuser", the user for connecting from Workspace Streaming
Example: CreateDatabaseSchema.cmd system manager orcl oracle "pass#123"
4. Restart the machine after executing the tool