11g ora-1555 on activated standby after migration from 10g

Switched over or failed over Standby is compromised by ora-1555.
Version 11.2.0.4
Migrated from 10.2.0.4

Thousands of standby index pages (blocks) corrupted by “Page 424697 failed with check code 6056″ as reported by dbv and the corrupted page count increasing. No active standby is involved. The corruptions were first detected on activated standby (on the mirror copy of the standby actually) and confirmed by dbv scan on the standby.
Opening standby in read only mode (no active data guard involved) and querying the corrupted indexes throws ora-1555 too.

So far only index blocks are corrupted.

No problems on primary. dbv report was clean on the primary.

Solution:

set _ktb_debug_flags = 8 on both primary and standby to enable the fix. Metalink doc Id: 8895202.8

THE FOLLOWING DISCOVERIES TOOK TIME AND EFFORT TO MAKE, BETTER READ THEM NOW:

DISCOVERY N# 1: set compatible=11.2.0 to enable the fix. The metalink document does not mention that.

DISCOVERY N# 2: set _ktb_debug_flags = 8 BEFORE querying corrupted standby database index. After the first ora-1555 pops out the fix has no effect (the block cleaning finalizes the corruption?). The error will continue to pop out. The index will have to be rebuilt.

DISCOVERY N# 3: That allone does not stop the corruptions. Corrupted blocks count is still increasing. To stop it:

1. Rebuild affected indexes (might use online option. it worked for us) on the primary database.

2. Format datafile blocks not part of dba_extents after index rebuild – this makes dbv report readable.

Root cause:

Root cause is unknown. Our observation and version of events are:

primary was compatible=10.2.0 and waiting for maintenance window to increase to 11.2.0

standby was set to compatible=11.2.0 as advised by Oracle support after the corruptions were detected. Corruptions are on the standby and the count is increasing.

indexes were rebuilt, corruptions stopped from occurring. No problems for a few weeks.

during the maintenance window:

primary was bounced with compatible=11.2.0
a switchover was performed.
the corruptions reappeared on new standby and the count increasing.
Some corrupted indexes where the same as the first time, some were a new ones.

Remember: no problems on old standby(now primary) after index rebuild on previous primary (now the current standby)

But the index rebuilt on the previous primary was done with compatible=10.2.0

ok, lets try index rebuilt one more time: it helped. Corruptions are cleaned and stopped occurring.

Our take:

11g redo apply on standby corrupts index blocks if 11g software works on 10g migrated database blocks. Parameter compatible=10.2.0 is a prerequisite for the corruption to occur.
Rebuilding indexes on 11g primary does something what prevents the corruptions being introduced on standby and clears existing corruptions on standby.
However: if primary is compatible=10.2.0 then the rebuild still leaves some structural issues in the index. After the switchover the redo apply process starts corrupting the previous standby indexes. Note that no problems occur on primary itself. It takes redo apply to introduce the corruption.
Rebuilding indexes on the new primary stopped the errors.
Not all indexes are affected. It looks like only indexes which are rather heavily used are corrupted. How heavily: for example, some our indexes have INIT_TRANS bumped from default to 20 for a reason of high ITL waits.

Question:
Why this problem should not occur during restore and recovery from backup ? I see no reason why it should not.

Final word:
while compatible=10.2.0 is such a tempting solution to keep the gates open to downgrade in case the migration is failure it comes at a cost for data guard.

Posted in Uncategorized | Leave a comment

licensing: force collection of dba_feature_usage_statistics

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=440761110506259&id=1291296.1&displayIndex=5&_afrWindowMode=0&_adf.ctrl-state=7k10hj6ru_37

– Force FUS collection
alter session set events ‘immediate trace name mmon_test level 6′;

Posted in Uncategorized | Leave a comment

v$rman_status slow 11gR2

SQL> exec dbms_stats.DELETE_TABLE_STATS(‘SYS’,’X$KCCRSR’)

SQL> select p, NULL, NULL from (select count(*) p from v$rman_status where operation = ‘BACKUP COPYROLLFORWARD’);

Posted in Uncategorized | Leave a comment

nls_length_semantics=CHAR again

Check nls_length_semantics=BYTE again. And again. And again.

When collecting statistics got  ORA-20002: Version of statistics table … is too old.
Please try upgrading it with dbms_stats.upgrade_stat_table

Know why ? Because the statistics table was created with nls_length_semantics=CHAR.

Oracle compares etalon table SYS.IMPDP_STATS with your stats table and yes, column sizes differ because SYS oh, yes has column size 30 but your table has (oh, yesss) column size 120. 

 

 

 

 

 

Posted in Uncategorized | Leave a comment

impdp import statistics failes after upgrade to 11g

If while importing statistics with impdp your are getting

ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 1, column 463:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 443:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 498:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 478:
PL/SQL: SQL Statement ignored
Failing sql is:
DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'CLASSIF';   t varchar2(60) := 'RLS_G
Job "CLASSIF"."SYS_IMPORT_FULL_01" completed with 7 error(s) at 09:17:53

then investigate Oracle Support article

DataPump Import (impdp) Raises ORA-942 When Importing Statistics (Doc ID 1313200.1)

Oracle upgrade changes SYS.IMPDP_STATS table from temporary into nomal table. The fix recreates the table by using
dbms_stats.create_stat_table ('SYS', 'IMPDP_STATS', null, true);

and grants
grant select, insert, delete on sys.impdp_stats to public;

Posted in Uncategorized | Leave a comment

LIKE operator and Underscore

Performance problem to be expected with LIKE-operator and underscore character being part of the query parameter, see a new example:

Assuming absolute majority of peoples are good, some are bad and few are ugly:


select * from folks where personality_type like 'THE_GOOD';
— the first fetch will return quickly

select * from folks where personality_type like 'THE_BAD'; -- slow

select * from folks where personality_type like 'THE_UGLY'; -- very slow

Thanks go to http://www.imdb.com/title/tt0060196/

 

Posted in Uncategorized | Leave a comment

11g slow drop tablespace including contents and…

Slow 11g drop tablespace including contents and datafiles.

Cause: Slow select with sql_id 7jrp85khm4tka:

select from ... INDCOMPART$, I_INDSUBPART_POBJSUBPART$, INDSUBPART$...

Solution (looks like it helped):

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats('SYS');
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

Solution description:

get rid of nested loops on tables mentioned above (INDCOMPART$,…)

Relevant Metalink notes:

Drop Tablespace Including Contents And Datafiles Takes Hours To Complete (Doc ID 438461.1)

DBMS_METADATA.GET_DDL: Query Against SYS.KU$_INDEX_VIEW Is Slow In 11.2.0.3 (Doc ID 1459841.1)

Posted in Uncategorized | Leave a comment