000027923 - KB-1567 Importing ACM data dump on Oracle 11g R2 EE

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000027923
Applies ToAffected Versions: 6.x; 5.X
IssueThis Knowledge base Applies to: Oracle 11g R2 enterprise edition. ONLY this version Oracle 11g R2 Enterprise edition

(**Standard Edition that ships with the Aveksa Appliance or Software installation, does not have this problem. ) 



Oracle 11g R2 enterprise edition contains a 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 may occur with any dump create on 11g R2 enterprise (only if importing to same version) . 



This can be seen with an error in the import log file and effects several of our tables, including

STMT_COMP_RESULTS, T_AV_DATA_ACCESS_COLLECTORS, T_AV_DADC_VERSIONS.



The error could look like 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 in as failures in the 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 UI 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
CauseRecreate table steps:





1) Recreate the Oracle 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. Run this SQL command, as sysdba, to drop the existing AVUSER, and then follow the steps in our Documentation to recreate this Oracle account with the required settings. Note also that if AVUSER has any active connections, the drop command will fail. In this case, the database instance should be restarted to ensure no active AVUSER connections, and then the drop command should be re-issued.



drop user AVUSER cascade



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



If the impdp command was run using the option schemas=avuser, remove that as that option cannot be used with the tables option. Running impdp with these options does NOT create any objects but puts all the ddl into a sql file.



3) Edit the file avuserddl.sql and remove the "deferred segment" portion of the create statement and save the file.



4) Run the avuserddl.sql file as avuser.



5) Run the ‘normal’ impdp command with this additional option - TABLE_EXISTS_ACTION=APPEND
Resolution
It is recommended that you create your DB instance with the deferred segment turned off before creating your schema. This will prevent the issue from occurring. This SQL command can be run as a sysdba, to change the default schema correctly.
alter system set DEFERRED_SEGMENT_CREATION = FALSE
However, if you have database dumps which were created on a system that has this issue, a different process would need to be followed to prevent the error from occurring. This process is detailed below. It must be followed BEFORE importing a .dmp file. .

 

Attachments

    Outcomes