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.
You should only run the Create Scripts against newly created databases. To upgrade a database to the required schema version for the CM4D application, please follow the instructions in the topic Upgrading a Database Schema. Both the Create and Update Scripts are provided with your CM4D Installation.
Similar procedures may be followed for Oracle 10g and 9i using SQL Plus.
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.
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.
The same task can also be accomplished
through SQL Plus. Below is a typical example:
CREATE SMALLFILE TABLESPACE "CM4D" DATAFILE
'C:\APP\ADMINISTRATOR\ORADATA\ORCL\cm4d' SIZE 500M AUTOEXTEND ON NEXT
20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO
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.
The same task can also be accomplished
through SQL Plus. Below is a typical example:
CREATE SMALLFILE TABLESPACE "CM4DSITE"
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\cm4dSITE' SIZE 20M AUTOEXTEND
ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO
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.
The Resource Role gives the CM4D user account enough power to create the schema within its Tablespace.
e. Click OK.
Below is a typical script to
accomplish the CM4D user creation task:
CREATE USER "CM4D" PROFILE "DEFAULT"
IDENTIFIED BY "*******" DEFAULT TABLESPACE "CM4D"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK GRANT "CONNECT"
TO "CM4D" GRANT "RESOURCE" TO "CM4D"
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.
The Resource Role gives the CM4DSITE user account enough power to create the schema within its Tablespace.
e. Click OK.
The same task can also be accomplished
through SQL Plus. Below is a typical example:
CREATE USER "CM4DSITE" PROFILE "DEFAULT"
IDENTIFIED BY "*******" DEFAULT TABLESPACE "CM4DSITE"
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK GRANT "CONNECT"
TO "CM4DSITE" GRANT "RESOURCE" TO "CM4DSITE"
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.
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.
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.
Before running any update scripts, refer to the topic Upgrading a Database Schema for information on specific upgrade paths and requirements.
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.
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.