Oracle Databases

In this topic: Hide

 

Oracle can be used as a database platform for CM4D. The following examples demonstrate how to create and update Oracle 11g databases in Oracle Enterprise Manager and SQL Developer. These are only simple examples and your local Oracle DBA should always be consulted.

Oracle Database Scripts

The scripts needed to create or upgrade an Oracle database are included with the CM4D Enterprise and Client Installations. Browse to the the Database Scripts\Oracle folder in your CM4D install directory.

When you examine the contents of the scripts directory you will see a number of scripts each designated with a version number and the database type. Scripts with the prefix UpdateSite are used to upgrade CM4D Site databases. The scripts without this prefix are used to upgrade the CM4D Datasource databases.

In addition to the Update Scripts, you will also notice additional scripts which are used to create a new empty database of each type. For instance, the Site Tables.sql, Site Procedures.sql, Site Grants.sql, and Site Indexes.sql scripts are used to create a clean empty CM4D Site database. Similarly the Tables.sql, Procedures.sql, Grants.sql, and Indexes.sql scripts are used to create clean empty CM4D Datasource databases.

Creating CM4D Oracle 11g Databases

1.     Login to Oracle Enterprise Manager.

2.     Select the Server tab.

3.     Select Tablespaces.

4.     Click Create to make a new Tablespace for the CM4D DataSource database.

a.     Enter a CM4D Tablespace name (e.g., cm4d).

b.     Click Add to add a Datafile to the Tablespace.

i.        Enter a Datafile Name (e.g., cm4d).

ii.      Set the initial File Size to 500MB.

(1)  Optionally, you can enable Automatically extend datafile when full.

(a)  Set to Increments of 20MB.

(b)  Leave the Maximum File Size as Unlimited.

iii.    Click Continue.

c.      Click OK.

5.     Click Create to make a second Tablespace for the CM4D Site database.

a.     Enter a Site Tablespace name (e.g., cm4dsite).

b.     Click Add to add a Datafile to the Tablespace.

i.        Enter a Datafile name (e.g., cm4dsite).

ii.      Set the initial File Size to 20MB.

(1)  Optionally, you can enable Automatically extend datafile when full.

(a)  Set to Increments of 5MB.

(b)  Leave the Maximum File Size as Unlimited.

iii.    Click Continue.

c.      Click OK.

6.     Select the Users tab.

7.     Click Create to make a new user to associate with the CM4D DataSource Tablespace (e.g., CM4D).

a.     Enter a user name and password for the first Tablespace (e.g., cm4d).

b.     Edit the Default Tablespace.

i.        Select the first Tablespace you created (e.g., CM4D), then click Select.

c.      Edit the Temporary Tablespace.

i.        Select the Temp Tablespace, then click Select.

d.     Select the Roles tab.

i.        Click Edit List.

(1)  Select Resource from the Available Roles and click OK.

e.     Click OK.

8.     Click Create to make a new user to associate with the CM4D Site Tablespace (e.g., CM4DSITE).

a.     Enter a user name and password for the first Tablespace (e.g., cm4d).

b.     Edit the Default Tablespace.

i.        Select the second Tablespace you created (e.g., CM4DSITE), then click Select.

c.      Edit the Temporary Tablespace.

i.        Select the Temp Tablespace, then click Select.

d.     Select the Roles tab.

i.        Click Edit List.

(1)  Select Resource from the Available Roles and click OK.

e.     Click OK.

Installing Oracle 11g CM4D Schemas

Once the Oracle 11g Database(s), or Tablespace(s), have been created, the Schema needs to be installed. For this example, we will use Oracle 11g SQL Developer.

Creating a Site Database

1.     Click Connect.

2.     Login as the user created for the CM4D Site database (e.g., CM4DSITE).

3.     Browse to the Site Tables.sql script and click Open.

4.     Click Run Script.

5.     Click OK.

6.     Monitor the script execution and look for any error messages.

7.     Repeat the previous steps for the Site Procedures.sql and Site Indexes.sql scripts.

8.     Disconnect the current user.

Creating a CM4D Database

1.     Connect as the user created for the CM4D DataSource database (e.g., CM4D).

2.     Browse to the Tables.sql script and click Open.

3.     Click Run Script.

4.     Click OK.

5.     Monitor the script execution and look for any error messages.

6.     Repeat the previous steps for the Procedures.sql and Indexes.sql scripts.

7.     Disconnect the current user.

Upgrading an Oracle 11g Database Schema

Updating a Site Database

Starting with your current Site database Schema version, you must run each script sequentially until you reach the latest Schema version. If you are not using a Site database, skip to the next section, Updating a CM4D Database.

1.     Login as the user created for the CM4D Site database (e.g., CM4DSITE).

2.     Browse to the UpdateSite[ ].sql script for the schema one version higher than your current schema, and click Open.

3.     Click Run Script.

4.     Click OK.

5.     Monitor the script execution and look for any error messages.

6.     Repeat the previous steps for any additional update scripts.

7.     Disconnect the current user.

Updating a CM4D Database

Starting with your current CM4D DataSource database Schema version, you must run each script sequentially until you reach the latest Schema version.

1.     Connect as the user created for the CM4D DataSource database (e.g., CM4D).

2.     Browse to the Update[ ].sql script for the schema one version higher than your current schema, and click Open.

3.     Click Run Script.

4.     Click OK.

5.     Monitor the script execution and look for any error messages.

6.     Repeat the previous steps for any additional update scripts.

7.     Disconnect the current user.