{"id":73123,"date":"2016-04-01T10:30:57","date_gmt":"2016-04-01T10:30:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/siebel-crm-part-6-configuring-the-siebel-database\/"},"modified":"2021-07-14T13:07:10","modified_gmt":"2021-07-14T13:07:10","slug":"siebel-crm-part-6-configuring-the-siebel-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/siebel-crm-part-6-configuring-the-siebel-database\/","title":{"rendered":"Siebel CRM Part 6: Configuring the Siebel Database"},"content":{"rendered":"<p>The final step before we can start the Siebel Server installation is to configure the database server. In <a href=\"https:\/\/allthingsoracle.com\/siebel-crm-part-5-the-ancillary-software\/\">the last article in this series<\/a> we completed the following tasks:<\/p>\n<ol>\n<li>Installation of the Oracle Database server on the Amazon server called Siebel Database Server.<\/li>\n<li>Creation of an Oracle Database called &#8220;SiebelDB&#8221;.<\/li>\n<li>Installation of the Oracle Client on all the servers.<\/li>\n<\/ol>\n<p>Before we can proceed with the Siebel Installation, we need to complete the following steps:<\/p>\n<ul>\n<li>Ensure that all Oracle clients are able to communicate with the Oracle Database Server.<\/li>\n<li>Create the Table Space and the Index space for the Siebel Installation.<\/li>\n<li>Run the grantusr.sql script to create Siebel-specific users.<\/li>\n<\/ul>\n<h2>Configuring the Oracle Client<\/h2>\n<p>Before we start the Oracle Client configuration, it is prudent to boot up all the servers in the Amazon instance. This is a good exercise for the following reasons:<\/p>\n<ol>\n<li>The TNS Listener and the other database services can recycle properly.<\/li>\n<li>Any blocked memory can be released.<\/li>\n<\/ol>\n<p>The sequence for the restart is as follows:<\/p>\n<ol>\n<li>Shut down the Siebel Web_Gateway_Server.<\/li>\n<li>Shut down the Siebel_Application_Server.<\/li>\n<li>Shut down the Siebel Database Server.<\/li>\n<li>Restart the Siebel Database Server and wait for all of the Oracle services to start up.<\/li>\n<li>Start the Siebel Web_Gateway_Server.<\/li>\n<li>Start the Siebel_Application_Server.<\/li>\n<\/ol>\n<p>The server shutdown and the restarts can be performed from the Amazon console.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image.png\" alt=\"\" \/><\/a><\/p>\n<p>Right Click on the Server name and choose the image state. This will give you the shutdown and restart options.<\/p>\n<p>Once the steps above are complete, log on to the Siebel Database server using terminal services.<\/p>\n<p>Navigate to &#8220;<code>D:\\Oracle_DB_Server\\product\\12.1.0\\dbhome_1\\NETWORK\\ADMIN<\/code>&#8220;.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-1.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-1.png\" alt=\"\" \/><\/a><\/p>\n<p>Copy the three &#8220;.ORA&#8221; files. These need to be copied to &#8220;<code>D:\\Oracle_Client\\product\\11.2.0\\client_1\\network\\admin<\/code>&#8221; on all the servers. Overwrite any existing files.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-2.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-2.png\" alt=\"\" \/><\/a><\/p>\n<p>Navigate to <code>Oracle-OraClient11Home1 -&gt; Application Development-&gt; SQL Developer<\/code>.<\/p>\n<p>If the Java client is set up properly with the correct environment variables, the SQL developer application should open up correctly without any issues.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-3.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-3.png\" alt=\"\" \/><\/a><\/p>\n<p>Right click on the connections and choose <strong>New Connection<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-4.png\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-4.png\" alt=\"\" \/><\/a><\/p>\n<p>In the new connection window, the connection options should be chosen as shown in the image above and described below:<\/p>\n<ol>\n<li>Connection Name: Can be anything<\/li>\n<li>Username: SYS (This is one of the few logins enabled during the Oracle Database server installation)<\/li>\n<li>Password: The password which was chosen during the Oracle Database server installation.<\/li>\n<li>Connection Type: Basic<\/li>\n<li>Role: SYSDBA. (This must be chosen otherwise you will be unable to logon as SYS)<\/li>\n<li>Hostname: Hostname of the Oracle Database Server.<\/li>\n<li>Port: 1521<\/li>\n<li>Service Name: SiebelDB<\/li>\n<\/ol>\n<p>Click on <strong>Test<\/strong>. If the setup has been done correctly, the Status should show a success.<\/p>\n<p>This test must be done from all the three servers to ensure that all the Oracle Clients on the servers are able to communicate with the Oracle Database Server. If SQL Developer opens up from all three servers and we are able to communicate, then the clients have been installed and configured correctly.<\/p>\n<h2>Create the Index and the Data Table Spaces<\/h2>\n<p>Oracle recommends that the Siebel tables and indexes be loaded into different table spaces, separately from the default Oracle tablespaces. This is done primarily to have control over performance and separate out the core database server functions from the Siebel Database operations.<\/p>\n<p>From any of the open SQL Developer sessions from the previous steps, run the following SQL Commands:<\/p>\n<pre>Create tablespace SBL_DAT Datafile \r\n'D:\\Oracle_DB_Server\\oradata\\SiebelDB\\SBL_DAT.DBF'\r\nSize 2048M\r\nAutoextend ON NEXT 200M\r\nMAXSIZE Unlimited\r\n\r\nCreate tablespace SBL_INDX Datafile 'D:\\Oracle_DB_Server\\oradata\\SiebelDB\\SBL_INDX.DBF'\r\nSize 2048M\r\nAutoextend ON NEXT 200M\r\nMAXSIZE Unlimited<\/pre>\n<p><a href=\"#\" target=\"_blank\"><img decoding=\"async\" style=\"max-width: 100%;\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2016\/04\/word-image-5.png\" alt=\"\" \/><\/a><\/p>\n<p>Execute the SQL and commit. This will create the table spaces for the indexes and the tables.<\/p>\n<h2>Grantusr Script<\/h2>\n<p>Grantusr.sql is a script which has been created by Oracle to create the default administrative Siebel users and grant them access to the tablespaces created in the previous steps. The file in its default format normally gets copied when we install Siebel. However, since we have not reached that step yet, we can use the SQL below to run it.<\/p>\n<pre>--------------------------------------------------------------------------------------------------------------------------\r\n\r\nrem $Header: \/v65\/datamodel\/release\/oracle\/grantusr.sql 1     7\/13\/00 4:44p Vchan $\r\nrem\r\nrem NAME\r\nrem     grantusr.sql\r\nrem\r\nrem Description\r\nrem     This script creates the Oracle database roles and users for\r\nrem     the Siebel Sales Enterprise.\r\nrem     There are two types of Oracle users:\r\nrem        1. Table Owner (default SIEBEL) which owns all the\r\nrem           database objects for the Siebel Sales Enterprise.\r\nrem           This account must have resource privilege on a tablespace.\r\nrem        2. User Account. These are the accounts that SSE users\r\nrem           login to. This account has select, insert, update privileges\r\nrem           on the tables in the SIEBEL table owner. The user accounts\r\nrem           access the database objects in the SIEBEL table owner via\r\nrem           the sse_role. \r\nrem\r\nrem NOTES (Please Read The Following First Before Executing This Script): \r\nrem   1. This script must be run by an Oracle DBA Account\r\nrem   2. You must run this script in SQL*Plus because it will prompt you\r\nrem      for the name of the siebel_tablespace. Alternatively, you can\r\nrem      edit this script to replace the &amp;siebel_tablespace so you can run\r\nrem      the script in SQL*DBA.\r\nrem   3. We assume there is only one tablespace (siebel_tablespace) hosting both Siebel tables \r\nrem      and indices. If you have additional tablespaces then you need to add the grant and \r\nrem      alter commands in the script below for each and every tablespace you create for your \r\nrem      Siebel implementation.\r\nrem   4. In Siebel 8.x, individual table owner grants have been replaced by a role called tblo_role.\r\nrem      It is easier to manage this role rather than individual grants, plus easier to create\r\nrem      and grant additional table owners (useful when having oltp and olap in one instance,\r\nrem      for example).  Also the new grants are to support Oracle 10gR2 and 11gR1; they contain\r\nrem      privileges from depricated connect and resource roles, as well as specific privileges\r\nrem      required by the Siebel application.\r\nrem\r\n\r\nrem ==================================================\r\nrem Create db account and roles for Siebel table owner\r\nrem ==================================================\r\n\r\nrem Create Role sse_role\r\ncreate role sse_role;\r\ngrant create session to sse_role;\r\n\r\nrem Create Role tblo_role\r\ncreate role tblo_role;\r\ngrant ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE INDEXTYPE,\r\n  CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION,\r\n  CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW,\r\n  CREATE DIMENSION, CREATE MATERIALIZED VIEW, QUERY REWRITE, ON COMMIT REFRESH\r\nto tblo_role;\r\n\r\n<span style=\"color: red;\">rem Create SIEBEL user and replace PASSWORD with what you want to put.<\/span>\r\ncreate user SIEBEL identified by PASSWORD;\r\ngrant tblo_role to SIEBEL;\r\ngrant sse_role to SIEBEL;\r\nalter user SIEBEL quota 0 on SYSTEM quota 0 on SYSAUX;\r\n<span style=\"color: red;\">rem siebel_tablespace needs to be replaced with SBL_DAT<\/span>\r\nalter user SIEBEL default tablespace &amp;&amp;siebel_tablespace;\r\n<span style=\"color: red;\">rem temp_tablespace needs to be replaced with TEMP<\/span>\r\n\r\nalter user SIEBEL temporary tablespace &amp;&amp;temp_tablespace;\r\nalter user SIEBEL quota unlimited on &amp;&amp;siebel_tablespace;\r\n<span style=\"color: red;\">rem siebel_indexspace needs to be replaced with SBL_INDX<\/span>\r\nalter user SIEBEL quota unlimited on &amp;&amp;siebel_Indexspace;\r\n\r\n\r\nrem ==================================================\r\nrem Create db accounts for Siebel users\r\nrem ==================================================\r\n\r\n<span style=\"color: red;\">rem Create SADMIN user and replace PASSWORD with what you want to put.<\/span>\r\ncreate user SADMIN identified by PASSWORD;\r\ngrant sse_role to SADMIN;\r\n<span style=\"color: red;\">rem siebel_tablespace needs to be replaced with SBL_DAT<\/span>\r\nalter user SADMIN default tablespace &amp;&amp;siebel_tablespace;\r\n<span style=\"color: red;\">rem temp_tablespace needs to be replaced with TEMP<\/span>\r\nalter user SADMIN temporary tablespace &amp;&amp;temp_tablespace;\r\n\r\n<span style=\"color: red;\">rem Create LDAPUSER user and replace PASSWORD with what you want to put.<\/span>\r\ncreate user LDAPUSER identified by PASSWORD;\r\ngrant sse_role to LDAPUSER;\r\n<span style=\"color: red;\">rem siebel_tablespace needs to be replaced with SBL_DAT<\/span>\r\nalter user LDAPUSER default tablespace &amp;&amp;siebel_tablespace;\r\n<span style=\"color: red;\">rem temp_tablespace needs to be replaced with TEMP<\/span>\r\nalter user LDAPUSER temporary tablespace &amp;&amp;temp_tablespace;<\/pre>\n<p>Please pay particular attention to the text in red. The script above is slightly different from the default grantusr script since we are using multiple tablespaces rather than the single tablespace the script is designed to use. The SQL is performing the following tasks:<\/p>\n<ol>\n<li>Creating the SSE Role. This is required for all Database users of the application.<\/li>\n<li>Creating the TBLO Role. This is the table owner role and used only for the SIEBEL USER.<\/li>\n<li>Creating the Siebel (Table Owner), SADMIN (System Administrator) and LDAPUSER (Standard user for LDAP) users.<\/li>\n<\/ol>\n<p>Execute the entire code above using SQL developer and commit the changes once executed. This will complete all the database activities. We are not yet ready for installing and configuring Siebel.<\/p>\n<p>In the next article in this series, we will start the installation of the Siebel application servers for the sandbox environment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The final step before we can start the Siebel Server installation is to configure the database server. In the last article in this series we completed the following tasks: Installation of the Oracle Database server on the Amazon server called Siebel Database Server. Creation of an Oracle Database called &#8220;SiebelDB&#8221;. Installation of the Oracle Client on all the servers. Befor&hellip;<\/p>\n","protected":false},"author":105470,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48498],"coauthors":[],"class_list":["post-73123","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-siebel-crm"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/105470"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73123"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73123\/revisions"}],"predecessor-version":[{"id":91620,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73123\/revisions\/91620"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73123"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}