000036060 - ORA-01578 ORACLE data block corrupted reported in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Apr 28, 2018•Last modified by RSA Customer Support Employee on May 2, 2018
Version 5Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000036060
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 6.9.1 and above
 
IssueProblems 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
Corrupt Block Found
TSN = 7, TSNAME = DATA_1M
RFN = 1024, BLK = 4761249, RDBA = 4761249
OBJN = 662559, OBJD = 662559, OBJECT = T_DC_SOURCEDATA_GROUP_MEM, SUBOBJECT =
SEGMENT OWNER = AVUSER, SEGMENT TYPE = Table Segment
Errors in file /u01/app/oracle/diag/rdbms/avdb/AVDB/trace/AVDB_ora_33002.trc (incident=106121):
ORA-01578: ORACLE data block corrupted (file # 8, block # 4761249)
ORA-01110: data file 8: '+DG01/avdb/datafile/data_1m.274.852963125'
Incident details in: /u01/app/oracle/diag/rdbms/avdb/AVDB/incident/incdir_106121/AVDB_ora_33002_i106121.trc


From the database alert_AVDB.log file, we also see the table name that has the corruption:
 
OBJECT = T_DC_SOURCEDATA_GROUP_MEM
CauseThe 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."


ResolutionThe following solutions apply to more situations than a problem with a Collection.

If the corruption is in an index


If the corruption is in an index, then the index can be dropped and re-created.

  1. Login to the system as the oracle user.
  2. As index creation can severely impact performance, it is best to stop RSA Identity Governance & Lifecycle:

# acm stop


  1. Login to the database as the Aveksa user:

# sqlplus avuser/password


  1. Using the object name from the corruption error in the Oracle database alert_AVDB.log, get the SQL to create the index:

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"


  1. Drop the index.  

SQL> DROP INDEX AVUSER.<index name>;


  1. Using the SQL statement from Step 4, create the index again:

SQL> CREATE INDEX "AVUSER". ...


  1. Exit SQL*Plus and start RSA Identity Governance & Lifecycle:

SQL> exit

# acm start



If the corruption is in a table


In a standard implementation of RSA Identity Governance & Lifecycle, customers can restore their database using the (export) backup.  The instructions are available in the Online Help under Administration > Managing the Appliance > Restoring the Database for an RSA Appliance.

The location of the (export) backup should be /home/oracle/AveksaExportImportDir. 

Please make sure that the date and time of the (export) backup is BEFORE the time the Oracle data block corruption was reported.

WorkaroundIf customers do not have an (Export) backup, or their (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?



  1. Login to the system as the oracle user.
  2. Connect to the database as sysdba:

# sqlplus / as sysdba


  1. Query the V$DATABASE table:

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG


  1. The value of LOG_MODE should be either ARCHIVELOG or NOARCHIVELOG.


NOARCHIVELOG mode options



In a standard implementation of RSA Identity Governance & Lifecycle, the AVDB database is in NOARCHIVELOG mode.  This means it does not have archived logs, but it also means Oracle cannot go back into the archived logs to get the block data before it was corrupted.  Therefore, our options are to rebuild the data or skip the corrupt blocks. 



Skipping corrupt blocks means data is lost, so it must be considered as a last resort.


 

I.  Rebuild the data



This option can only be used if the table data can be recollected.  The following table lists the AVUSER schema tables that can be recollected and the specific Collectors that need to be run.
Tables to be recollected
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_USERIdentity Collectors
T_DC_SOURCEDATA_CHANGE_LOG
   T_DC_SOURCEDATA_REJECT_LOG		All Collectors



  1. Login as the oracle user. 
  2. Make sure no-one is using Identity Governance & Lifecycle:

# acm stop


  1. Login to the Oracle database as AVUSER:

sqlplus avuser/<pwd>


  1. 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. 

SQL> TRUNCATE table <table name> DROP ALL STORAGE; 


  1. For the Collector type specified by the Tables to be recollected table above, set them to run a Full Refresh for the next run:

SQL> UPDATE t_data_collectors set requires_full_refresh = 'R'  WHERE name IN ('<Collector Name 1>', ...);


  1. Exit out of SQL and start Identity Governance & Lifecycle:

SQL> exit

# acm start


  1. Rerun all the Collectors that were updated by Step 5. 
  2. 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.




In Oracle Support Note 556733.1 it states the following:


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.



  1. Login as the oracle user. 
  2. Make sure no-one is using Identity Governance & Lifecycle:

# acm stop 


  1. Login to the Oracle database as sysdba:

# sqlplus / as sysdba


  1. "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:  

BEGIN
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;
/


  1. Exit out of SQL and start Identity Governance & Lifecycle: 

SQL> exit
# 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.  


  1. Login as the oracle user. 
  2. Make sure no-one is using Identity Governance & Lifecycle: 

# acm stop


  1. Review Oracle Support Note 28814.1 - Handling Oracle Block Corruptions.
  2. 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.

Implement one of these options to correct the corruption.


  1. After Complete Recovery, check that the corrupt table is OK by running the following command, where you will be prompted for the table name.

SQL> ANALYZE &table_name VALIDATE STRUCTURE CASCADE


  1. Start RSA Identity Governance and Lifecycle:

# acm start

Attachments

    Outcomes