Creating the database and schema for an Oracle database

You can create the database and schema for an Oracle database by completing the database configuration assistant wizard.

Follow these steps:

1. Create the database.
   1. (UNIX, Linux, or AIX® only) Log on to the graphical console as the Oracleaccount. Where Oracleaccount is the account where the Oracle server is installed.
   2. Run this command from the command prompt: dbca
   3. In the Database Configuration Assistant : Welcome panel, click Next.
   4. Select Create a Database and click Next.
   5. The template General Purpose of Transaction Processing is selected by default. Click Next.
   6. Provide values for these fields: Global Database Name and SID. For example, rapmdb.
   7. In the Management options panel, review if Configure Enterprise Manager and Configure Database Control for local management are selected. Click Next.
   8. If you see a warning message to add a listener:
      1. Run the netca command from the command prompt.
      2. Follow the instructions in the Oracle Net Services Configuration panel.
   9. In the Database Credentials panel, select Use the Same Administrative Password for All Accounts and enter the password. Click Next.
   10. In the Database File Locations panel, review that the Use Database File Locations from Template is selected by default and click Next.
   11. Accept the default selections in Recovery Configuration panel and click Next.
   12. Click Next on the Database Content panel.
   13. In the Initialization Parameters panel, select the amount of memory for the database under the Memory tab. Ensure that this value meets the minimum requirement for the IBM Security Role and Policy Modeler database on Oracle.
   14. In the Initialization Parameters panel, select Use Unicode under the Character Sets tab.
   15. In the Initialization Parameters panel, click All Initialization Parameters and set the open_cursors parameter value to 300 or higher. Click Close.
   16. In the Database Storage panel, expand Redo Log Groups, and then change the file size for each redo log group to 200 MB. When finished, click Next.
   17. In the Creation Options panel, review if Create Database is selected. Click Finish.
   18. The Confirmation dialog box is displayed with Create Database Summary. Click OK.
2. Run the netca command, if you did not run it previously.
3. Run this command from the command prompt: sqlplus /nolog
4. Connect to the database. Run these commands:

   connect oracleadminuser/Oracleadminpassword@databasename

   Where databasename is the IBM Security Role and Policy Modeler database name. For example rapmdb.

   create tablespace Oracleuser
   datafile 'Oracleuser_001.dbf'
   size 64M reuse
   autoextend ON
   next 64M
   maxsize unlimited
   default storage (initial 10M next 1M pctincrease 10);
   
   create user Oracleuser identified by Oracleauserpassword
   default tablespace Oracleuser
   quota unlimited on Oracleuser;
   
   grant create session to Oracleuser;
   
   grant create table to Oracleuser;
   
   grant create any procedure to Oracleuser;
   
   grant create view to Oracleuser;
   
   create or replace trigger nls after logon on database
   begin execute immediate 'alter session set NLS_COMP=ANSI';
   execute immediate 'alter session set NLS_SORT=GENERIC_M';
   end;
   /

5. To create the schema, you must disconnect the database connection. Run this command:

   SQL> disconnect

6. Connect to the database to run the script files. Run this command:

   SQL> connect Oracleuser/Oracleuserpassword@databasename

7. To create the schema, run these script files from Oracle SQL prompt with these commands:
   • Microsoft Windows operating system:

     SQL> @"Script location\security_modeling_schema_oracle.sql"

   • UNIX, Linux, or AIX operating systems:

     SQL> @"Script location/security_modeling_schema_oracle.sql"

8. Modify the IBM Security Role and Policy Modeler Oracle database schema. Run one of these commands:
   • Windows operating system:

     SQL> @"script_location\security_modeling_schema_modify_oracle_1.1.0.1.sql"

     For example:

     SQL> @"C:\SecurityModeler\scripts\security_modeling_schema_modify_oracle_1.1.0.1.sql

   • UNIX, Linux, or AIX operating systems:

     SQL> @"script_location/security_modeling_schema_modify_oracle_1.1.0.1.sql"