Article Content
Article Number | 000036060 | ||||||||||||||||
Applies To | RSA Product Set: RSA Identity Governance & Lifecycle RSA Version/Condition: 6.9.1 and above | ||||||||||||||||
Issue | Problems may arise with Collections due to data not being collected properly. Specifically, the aveksaServer.log may contain an error from the Oracle database. For example, 01/18/2018 06:27:17.368 ERROR (Exec Task Consumer#0) [com.aveksa.server.xfw.TaskExecutor] Failed method=Execute ExecutionTask[TaskID=451004 RunID=null Source=9 Type=DataProcessing Status=InProgress] com.aveksa.server.xfw.ExecutionException: com.aveksa.server.collector.DataProcessorException: com.aveksa.server.db.PersistenceException: java.sql.SQLException: ORA-01578: ORACLE data block corrupted (file # 8, block # 4761249) ORA-01110: data file 8: '+DG01/avdb/datafile/data_1m.274.852963125' ORA-06512: at "AVUSER.COLLECTOR_DATA_TABLES", line 92 ORA-06512: at "AVUSER.ADC_EXTERNAL_TABLES", line 142 ORA-06512: at "AVUSER.ACCOUNT_DATA_COLLECTOR", line 132 ORA-06512: at "AVUSER.ACCOUNT_DATA_COLLECTOR", line 267 ORA-06512: at line 1 An examination of the alert_AVDB.log, will show a corresponding error. Thu Jan 18 06:27:16 2018 From the database alert_AVDB.log file, we also see the table name that has the corruption: OBJECT = T_DC_SOURCEDATA_GROUP_MEM | ||||||||||||||||
Cause | The Oracle database has a block corruption. # oerr ora 1578 01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)" // *Cause: The data block indicated was corrupt. This was a physical // corruption, also called a media corruption. The cause is unknown // but is most likely external to the database. If ORA-26040 is also // signaled, the corruption is due to NOLOGGING or UNRECOVERABLE // operations. // *Action: The general method to repair a media corrupt block is to restore // a backup and recover the backup. For databases in ARCHIVELOG // mode, use block media recovery or media recovery. In some situations, // you can also drop the segment and re-create it. For example, you can // drop an index and re-create the index. For more information about Oracle database blocks, please review the Oracle Database Concepts guide, for the chapter on "Oracle Database Storage Structures." | ||||||||||||||||
Resolution | The following solutions apply to more situations than a problem with a Collection.If the corruption is in an indexIf the corruption is in an index, then the index can be dropped and re-created.
# acm stop
# sqlplus avuser/password
set long 1000000000 set pagesize 900 SELECT sys.dbms_metadata.get_ddl('<object-type>','<object-name>','<schema>') FROM dual; For example, to get the DDL for Index AVUSER.T_DC_SOURCEDATA_USER_1, execute the following: SQL> SELECT SYS.DBMS_METADATA.GET_DDL('INDEX','T_DC_SOURCEDATA_USER_1','AVUSER') from dual; SYS.DBMS_METADATA.GET_DDL('INDEX','T_DC_SOURCEDATA_USER_1','AVUSER') -------------------------------------------------------------------------------- CREATE INDEX "AVUSER"."T_DC_SOURCEDATA_USER_1" ON "AVUSER"."T_DC_SOURCEDATA_USER" ("RUN_ID", "DC_ID", "USER_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "INDX_1M"
SQL> DROP INDEX AVUSER.<index name>;
SQL> CREATE INDEX "AVUSER". ...
SQL> exit # acm start
| ||||||||||||||||
Workaround | If you do not have an (export) backup, or thei(export) backup is AFTER the time the Oracle data block corruption was reported, then the following options are available. However, first we need to determine if the database is in ARCHIVELOG mode or not.How to determine if an Oracle database is in ARCHIVELOG mode?
# sqlplus / as sysdba
SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG
|
If this table is being reported by the ORA-01578 error | Then these are the Collectors that need to be run |
---|---|
T_DC_SOURCEDATA_ACCOUNT T_DC_SOURCEDATA_ACCOUNT_MAP | Account Collectors, Account data |
T_DC_SOURCEDATA_GROUP T_DC_SOURCEDATA_GROUP_MEM | Account Collectors, Group Data |
T_DC_SOURCEDATA_ENTITLEMENT T_DC_SOURCEDATA_APPROLE T_DC_SOURCEDATA_APPROLE_MEM T_DC_SOURCEDATA_USER_ENT T_DC_SOURCEDATA_USER_ENT_MD T_DC_SOURCEDATA_RESOURCE* T_DC_SOURCEDATA_SUGG_RES_OWN* * Used in two Collectors | Entitlement Collectors |
T_DC_SOURCEDATA_ROLE T_DC_SOURCEDATA_ROLE_DEF T_DC_SOURCEDATA_ROLE_MEM T_DC_SOURCEDATA_ROLE_MEM_MD | Role Collectors |
T_DC_SOURCEDATA_RESOURCE* T_DC_SOURCEDATA_SUGG_RES_OWN* * Used in two Collectors | DAG Collectors |
T_DC_SOURCEDATA_USER | Identity Collectors |
T_DC_SOURCEDATA_CHANGE_LOG T_DC_SOURCEDATA_REJECT_LOG | All Collectors |
- Login as the oracle user.
- Make sure no-one is using Identity Governance & Lifecycle:
- Login to the Oracle database as AVUSER:
- Truncate the table, removing all the Segments, including the corrupt blocks. Please note that Oracle will re-format the corrupt blocks when it re-uses them.
- For the Collector type specified by the Tables to be recollected table above, set them to run a Full Refresh for the next run:
- Exit out of SQL and start Identity Governance & Lifecycle:
# acm start
- Rerun all the Collectors that were updated by Step 5.
- Check the aveksaServer.log and Oracle database alert_AVDB.log for any corruption errors.
II. Skip the Corrupt Blocks
Use this option if the table data cannot be re-collected.
Skipping corrupt blocks means data is lost, so it must be considered as a last resort.
NOTE: The following information is taken from the Oracle Support notes listed below, where you will need to login to the Oracle Support portal to be able to access them.
If the goal is to skip the corrupt blocks for a specific object, it is just needed to run procedure SKIP_CORRUPT_BLOCKS. Only blocks producing ORA-1578 will be skipped in that case. If different errors are produced then it is required to run these additional procedures: ADMIN_TABLES, CHECK_OBJECT and FIX_CORRUPT_BLOCKS.
- Login as the oracle user.
- Make sure no-one is using Identity Governance & Lifecycle:
- Login to the Oracle database as sysdba:
- "Allow future DML statements to skip the corrupted blocks". Please note that you will need to provide the "schema_name" (i.e. AVUSER) and the "object_name" (i.e. the table name) from the ORA-1578 error in the Oracle database alert_AVDB.log file:
DBMS_ REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/
- Exit out of SQL and start Identity Governance & Lifecycle:
# acm start
ARCHIVELOG mode options
This option should only apply to customer-supported Oracle databases. This is because the RSA supported Oracle database does not have ARCHIVELOG mode enabled. Due to their complex nature, the steps will not be reproduced here.
NOTE: The following information is taken from the Oracle Support note listed below, where you will need to login to the Oracle Support portal to be able to access them.
- Login as the oracle user.
- Make sure no-one is using Identity Governance & Lifecycle:
- Review Oracle Support Note 28814.1 - Handling Oracle Block Corruptions.
- Go to section (5A) Complete Recovery, where the following options are available;
- RMAN Block Media Recovery. Use this option if the customer takes regular RMAN backups of their AVDB database.
- Datafile Recovery. Use this option if the customer copies their datafiles to another location as a backup.
- Database Recovery. Use this option if the customer has many datafiles that are corrupt.
- After Complete Recovery, check that the corrupt table is OK by running the following command, where you will be prompted for the table name.
- Start RSA Identity Governance and Lifecycle: