ORA-14223: Deferred segment creation is not supported for this table when importing RSA Identity Governance & Lifecycle data dump on Oracle 11g R2 EE

[#1]

Article Number

000027923

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 6.x
RSA Platform: Enterprise systems only

Issue

When importing a database .dmp file, the following error may occur:
 

ORA-14223: Deferred segment creation is not supported for this table  

 
Affected tables are:

STMT_COMP_RESULTS,
T_AV_DATA_ACCESS_COLLECTORS,
T_AV_DADC_VERSIONS,
T_AV_STMT_COMP_RESULTS

There may be an error in the database import log similar to this:

ORA-39083: Object type TABLE:"AVUSER"."T_AV_STMT_COMP_RESULTS" failed to create with error:
ORA-14223: Deferred segment creation is not supported for this table
Failing sql is:
CREATE TABLE "AVUSER"."T_AV_STMT_COMP_RESULTS" ("ID" NUMBER NOT NULL ENABLE,
"COMPONENT_ID" NUMBER NOT NULL ENABLE, "JRXML" CLOB, "RR_XML" CLOB, "PRINT_OBJ" BLOB,
"RR_NT" "AVUSER"."RES_ROWS_T" )
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING TABLESPACE "DATA_1M" NESTED TABLE "RR_NT" STOR
ORA-39083: Object type TABLE:"AVUSER"."T_AV_DATA_ACCESS_COLLECTORS" failed to create with error:
ORA-14223: Deferred segment creation is not supported for this table
Failing sql is:
CREATE TABLE "AVUSER"."T_AV_DATA_ACCESS_COLLECTORS" ("ID" NUMBER(*,0) NOT NULL ENABLE, "APP_RESOLUTION_RULES" CLOB NOT NULL ENABLE, "USER_NORM_RULE_SPEC" "XMLTYPE") SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "DATA_50M" LOB ("APP_RESOLUTION_RULES") STORE AS BASICFILE (
ORA-39083: Object type TABLE:"AVUSER"."T_AV_DADC_VERSIONS" failed to create with error:
ORA-14223: Deferred segment creation is not supported for this table;

These errors may not appear as failures in the database import.log when importing the database dmp file, but may be noticed later, as an error during migration of the database. These would appear in the User Interface Migration screen output as well as in the migrate.log file.

An example of the post-import, migration error is shown here:

begin Report_Views_Pkg.create_av_report_views; end;
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 328, ORA-06512: at line 1

Cause

Oracle 11g R2 Enterprise Edition contains an Oracle bug that appears on importing of a dump because of the default way a table could be created with deferred segments. (This bug has been fixed in Oracle Enterprise versions greater than 11g R2.) 

This affects only:

•   Pre-7.x RSA Identity Governance & Lifecycle installations since Oracle 12 is required for 7.x.
•   Non-appliance implementations only (the appliance and soft appliance ship with Oracle 11g Standard edition so they are not affected. This affects Enterprise Edition only.)

 

Resolution

This bug has been fixed in Oracle Enterprise versions greater than 11g R2.  Upgrade to a supported version of RSA Identity Governance & Lifecycle (7.x) which requires Oracle 12.




 

Workaround

It is recommended that you create your database instance with the deferred segment turned off before creating your schema. This will prevent the issue from occurring. Run the following SQL command as sysdba:

SQL> ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE

However, if you have database dumps which were created on a system that has this issue (i.e. deferred_segment_creation=TRUE) , a different process is needed to prevent the error from occurring. This process is detailed below and recreates the tables prior to the import. It must be followed BEFORE importing a .dmp file. 
 

1. Recreate the AVUSER:

Note that this step will drop any existing data, but this should not be a problem, as the intent is to import an existing database dump file.

As sysdba, run the commands below to drop the existing AVUSER, and then follow the steps in the correct version of the RSA Identity Governance and Lifecycle Database Setup and Management Guide for your deployment to recreate this Oracle account with the required settings.
 

If AVUSER has any active connections, the drop command will fail. In this case, the database instance should be restarted to ensure there are no active AVUSER connections, and then the drop command should be re-issued.

$ acm stopdb
$ acm startdb
$ sqlplus / as sysdba
SQL> DROP USER AVUSER CASCADE;
SQL> CREATE USER AVUSER identified by <password> profile ACMPROFILE;
SQL> ALTER USER AVUSER default tablespace DATA_1M temporary TABLESPACE TEMP;

2. Run the normal impdp command with these additional options: 

sqlfile=avuserddl.sql tables=avuser.T_AV_STMT_COMP_RESULTS, avuser.T_AV_DATA_ACCESS_COLLECTORS, avuser.T_AV_DADC_VERSIONS, avuser.T_AV_STMT_COMP_RESULTS

Running impdp with these options does NOT create any objects in the database. Instead it puts all the DDL into a SQL file called avuserddl.sql.

If the impdp command was run using the option schemas=avuser, do not use that option in this step, as that option cannot be used with the tables option. 

3. Edit the avuserddl.sql file and remove the deferred segment portion of the CREATE statement and save the file.
4. Connect as avuser and run the avuserddl.sql file .

$ sqlplus avuser/<password>
SQL> @avuserddl.sql

5. Now you are ready to import the database .dmp file. Run the ‘normal’ impdp command with this additional option:

TABLE_EXISTS_ACTION=APPEND