Database Requirements

Finding a Certified Database

Refer to the certification document for 12c (12.2.1.3.0) on the Oracle Fusion Middleware Supported System Configurations page for the latest information on certified databases.

Parent topic: Database Requirements

Configuring Your Oracle Database for the Metadata Services (MDS) Schema

On Oracle databases, the MDS database user created by Repository Creation Utility (RCU) requires EXECUTE privilege on DBMS_OUTPUT, DBMS_STATS, and DBMS_LOB. When you create a metadata repository using RCU, if PUBLIC does not have EXECUTE privilege on DBMS_OUTPUT and DBMS_STATS, the RCU user must have the privilege to grant EXECUTE privilege on DBMS_OUTPUT and DBMS_STAT to the MDS user. The RCU user must have the privilege to grant EXECUTE privilege on DBMS_LOB to the MDS user.

To ensure that you have the correct privileges, login to RCU as a SYSDBA or as a DBA user who has EXECUTE privilege with GRANT OPTION on DBMS_OUTPUT and DBMS_LOB.

Parent topic: Database Requirements

Assigning a Default Microsoft SQL Server Database for the Oracle Data Integrator Standalone Agent

A default database must be assigned to successfully complete the standalone agent startup with Microsoft SQL server. Run the following MSSQL statement to make this assignment:

ALTER LOGIN sql_login WITH DEFAULT_DATABASE = default_database

Replace sql_login with the login name, and default_database with the default database name.

Parent topic: Database Requirements

Configuring a Microsoft SQL Server Database for the Metadata Services (MDS) Schema

Note the following about using SQL Server as the database for MDS:

To create a metadata repository in SQL Server, set READ_COMMITTED_SNAPSHOT to ON for the hosting database. This enables the needed row versioning support. Use the following SQL command ALTER DATABASE, as in the following example:
```
ALTER DATABASE mds SET READ_COMMITTED_SNAPSHOT ON
```
Use case-sensitive collation to support the case-sensitive semantics in the metadata repository. For example, if Latin1_General is used, select the SQL_Latin1_General_CP1_CS_AS collation using the following SQL command:
```
ALTER DATABASE mds COLLATE SQL_Latin1_General_CP1_CS_AS
```
In many cases, this command will run successfully. However, the command might fail and generate error messages concerning functions, primary keys, constraints, or indexes. This can be caused if the database already has collation aware objects. In this case, SQL Server does not allow you to change the collation at the database level. In this case, the alternative is to create a new database with the expected collation for MDS to use.
There are some minor differences between an Oracle schema and a SQL Server schema. The length of the certain text fields are shorter for a SQL Server schema. For example, the full path name of the metadata in SQL Server is limited to 400 characters.

Parent topic: Database Requirements

Using Oracle Database 12c with Oracle Identity Management

The following packages must be installed as SYS user on Oracle databases prior to creating Oracle Identity Management schemas:

DBMS_SHARED_POOL
XAVIEWS

To create the above packages, run the below SQL files from the $ORACLE_HOME/rdbms/admin directory as the SYS user for the connected database (regular or PDB).

dbmspool.sql
prvtpool.plb
xaview.sql

See:

XAVIEWS — Using JDBC XA Drivers with WebLogic Server in Oracle Fusion Middleware Programming JTA for Oracle WebLogic Server.
DBMS_SHARED_POOL — Oracle Database PL/SQL Packages and Types Reference.

Parent topic: Database Requirements

Configuring a DB2 Database for the Metadata Services (MDS) Schema

Note the following about using DB2 as the database for MDS:

DB2 9.7 or later must be used for MDS repository to work properly.
Make sure that CUR_COMMIT is set to ON, which is the default value for a newly installed DB2 9.7 database. This setting can be verified using following DB2 command:
```
db2 connect to your_database user admin_user
db2 "get db cfg" | grep -I commit
Curently Committed   (CUR_COMMIT) = ON
```
Set the lock timeout parameter of the database to a low value. Unlike Oracle databases, with DB2, if one user is updating a row, under some conditions, another user may be blocked when updating a different row and must wait until the transaction is committed or rolled back by the first user. To facilitate better concurrency, do not specify -1, which sets the lock timeout to infinity.

To query the lock timeout value for your DB2 database, use the following command:
```
db2 'get database config for database_alias' | grep -i timeout
```
If the value is too high, change it. For example, to change the lock timeout value to 180 seconds, use the following command:
```
db2 'update database config for database_alias using locktimeout 180'
```
Choose a proper value for the locktimeout parameter. If a large value is used, it will affect system throughput, since the transaction has to wait a very long time before giving up. If the value is set too small, users will see many timeout exceptions if the database is processing many long running transactions.
Set the DB2 registry variables DB2_EVALUNCOMMITTED, DB2_SKIPINSERTED, and DB2_SJIPDELETED to OFF to avoid deadlock and locking issues. By default, they are set to OFF. To view the current registry variables setting, use the db2set -all command.

If they are not set to OFF, use the following commands:
```
db2set DB2_EVALUNCOMMITTED=OFF
db2set DB2_SKIPINSERTED=OFF
db2set DB2_SKIPDELETED=OFF
```
Then, restart the database server using the using db2stop and db2start commands.
DB2 may escalate a row lock to a table lock due to memory stress or lock usage. As the result, a user's transaction may be rolled back as a victim of deadlock or lock timeout. To reduce lock escalation, you can increase the size of the MAXLOCKS and LOCKLIST configuration parameters. Use the following commands:
```
db2 'update database config for database_alias using locklist value'
db2 'update database config for database_alias using maxlocks value'
```
The recommended value for MAXLOCKS is 20, and the recommended value for LOCKLIST is 70000. You should set appropriate values based on your environment and usage.
The isolation level must be set to Currently Committed. To verify the setting, use the following command:
```
db2 'get database config for database_alias' | grep -i commit
```
To set the isolation level to Currently Committed, use the following command:
```
db2 'update database config for database_alias using CUR_COMMIT ON'
```
If the database transaction log is often full, increase the database configuration parameter to allow for a larger log file. A larger log file requires more space, but it reduces the need for applications to retry the operation. You should set the log file size to at least 10000 and the number of primary log files to at least 50. Use the following commands:
```
db2 'update database config for database_alias using LOGFILSIZ 10000'
db2 'update database config for database_alias using LOGPRIMARY 50'
```
If the IBM DB2 JCC driver is chosen for the data source, the progressiveStreaming=2 property must be added to the property list of the data source. In effect, progressive streaming will be disabled. Without this property setting, MDS may run into issues in accessing document contents saved in a large object (LOB) column in the MDS repository.

Parent topic: Database Requirements