Oracle Cloud Infrastructure Database System with Oracle Grid Infrastructure

Oracle Grid Infrastructure uses Automatic Storage Management (ASM). The Oracle Data Pump import utility attempts to recreate the database files according to the location where they are stored. Since DBCS uses a file system, as opposed to ASM, the Oracle Data Pump import utility will create the dump file with the location of the files based on where they are physically stored on the disk. Therefore, if you attempt to import this dump file into the Oracle Grid Infrastructure, the import will fail to import any of the JRF tablespaces or schemas.

To avoid this failure, you must recreate the tablespaces and schemas for each Oracle WebLogic Server JRF domain that uses the DBCS system.

ssh to the Oracle Java Cloud Service administration host for the domain (the host name will end in wls-1) as the opc user and follow these instructions:

ssh to the Oracle Java Cloud Service administration host for the domain (the host name will end in wls-1) as the oracle user:
```
sudo su -oracle
```
```
vi /tmp/create_sql_for_jrf_schemas.py
```

Generate the SQL script to create the tablespaces and schemas.

Copy the following create_sql_for_jrf_schemas.py script to the /tmp directory:

import os
import sys
from xml.dom import minidom
import weblogic.security.internal.SerializedSystemIni as SerializedSystemIni
import weblogic.security.internal.encryption.ClearOrEncryptedService as ClearOrEncryptedService

def get_node_text(node):
    nodelist = node.childNodes
    for node in nodelist:
        if node.nodeType == node.TEXT_NODE:
            return node.data

if len(sys.argv) != 2:
    print("Supply the domain path as the only argument.")
    sys.exit(1)

domain_path = sys.argv[1]
# Get the schema prefix and pass from OPSS data source. The assumption is that all the values for the JRF schemas are the same.
ds_name = 'opss-data-source'
readDomain(domain_path)
cd('/JDBCSystemResource/' + ds_name + '/JdbcResource/' + ds_name + '/JDBCDriverParams/NO_NAME_0/Properties/NO_NAME_0/Property/user')
schema_prefix = get('Value').split('_')[0].upper()

# wlst does not allow reading of the password so parse directly from the config file
doc = minidom.parse(domain_path + '/config/jdbc/opss-datasource-jdbc.xml')
driver_params = doc.getElementsByTagName('jdbc-driver-params')[0].childNodes
for param in driver_params:
    if param.nodeName == "password-encrypted":
        system_ini = SerializedSystemIni.getEncryptionService(domain_path)
        encryption_service = ClearOrEncryptedService(system_ini)
        pass_phrase = encryption_service.decrypt(get_node_text(param))
        break
    if param.nodeName == "password":
        pass_phrase = get_node_text(param)

current_dir = os.getcwd()
sql_file = open(current_dir + '/create_jrf_data.sql', 'w')
# Create the tablespaces
sql_file.write("create tablespace " + schema_prefix + "_IAS_OPSS datafile '+DATA' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_IAU datafile '+DATA' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_MDS datafile '+DATA' SIZE 104857600 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_IAS_UMS datafile '+DATA' SIZE 104857600 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_STB datafile '+DATA' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_WLS datafile '+DATA' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
sql_file.write("create tablespace " + schema_prefix + "_IAS_TEMP datafile '+DATA' SIZE 62914560 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;\n")
# Create a required role stored in those tablespaces
sql_file.write("create role APEX_GRANTS_FOR_NEW_USERS_ROLE;\n")
# Create the schema users
sql_file.write("create user " + schema_prefix + "_OPSS identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_IAU identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_UMS identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_WLS_RUNTIME identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_WLS identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_MDS identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_IAU_VIEWER identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_IAU_APPEND identified by " + pass_phrase + ";\n")
sql_file.write("create user " + schema_prefix + "_STB identified by " + pass_phrase + ";\n")
# Grant unlimited quota to most important schema users on specific tablespaces
sql_file.write("alter user " + schema_prefix + "_OPSS quota unlimited on " + schema_prefix + "_IAS_OPSS;\n")
sql_file.write("alter user " + schema_prefix + "_UMS quota unlimited on " + schema_prefix + "_IAS_UMS;\n")
sql_file.write("alter user " + schema_prefix + "_WLS_RUNTIME quota unlimited on " + schema_prefix + "_WLS;\n")
sql_file.write("alter user " + schema_prefix + "_WLS quota unlimited on " + schema_prefix + "_WLS;\n")
sql_file.write("alter user " + schema_prefix + "_MDS quota unlimited on " + schema_prefix + "_MDS;\n")
sql_file.write("alter user " + schema_prefix + "_STB quota unlimited on " + schema_prefix + "_STB;\n")
sql_file.write("commit;\n")
sql_file.close()

As the oracle user, execute the script:
```
/u01/app/oracle/middleware/oracle_common/common/bin/wlst.sh /tmp/create_sql_for_jrf_schemas.py <path to your domain>
```
The script will place a create_jrf_data.sql file in your current directory.

Execute the SQL script on the Oracle Grid Infrastructure Oracle Cloud Infrastructure Database System.
1. Copy this script or the contents of the script to the Oracle Grid Infrastructure Oracle Cloud Infrastructure Database System.
2. Make the script accessible to the oracle user.
3. Log into the database as a user with the sysdba role.
```
sudo su - oracle
sqlplus
<At the prompt enter>:
sys as sysdba
<enter the password when prompted>
```
4. Switch to the target PDB on the Oracle Cloud Infrastructure Database System.
```
alter session set container = pdb_name_for_OCI_DB;
@<path to generated sql file>
```
  For example:
```
alter session set container = PDB1;
@/home/oracle/create_jrf_data.sql
```
Repeat these steps for each Oracle Java Cloud Service instance that uses the DBCS instance.