SMON rollback/transaction recovery disable, drop object

monitor recovery:

select * from V$FAST_START_TRANSACTIONS;

disable, drop, enable:

alter system set “_smu_debug_mode”=1024 ;

bounce db (?)

drop table …

remove “_smu_debug_mode”

bounce db

Recovery still going on.

Trying event:

event=”10513 trace name context forever, level 2″
event=”10061 trace name context forever, level 10″
fast_start_parallel_rollback=false

Bounce

drop the table

remove parameters and bounce

Results: if started with the beforementioned events set it skips recovery (no record in v$fast_start_transaction), it shutdowns immediate but after comming w/o events the txn is still there and locking on MMON – for that one see note

SMON Is Waiting On Latch High CPU Resource consumption MMON blocking SMON (Doc ID 1496453.1)

and workarround:

alter system set “_smu_debug_mode”=134217728;

(and kill mmon, database survives it)

 

Posted in Uncategorized | Leave a comment

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.

Afterword:
after switchover of 11.2.0 compatible standby and primary dbv detected failures again on the standby. The failure count is increasing on each dbv run (2000 and counting in our case). dbv had found not failures on both standby and primary before the switchover. Rebuild of indexes cleans the errors for good.

Out take:
Compatible 11.2.0 or 10.2 the block ITL corruptions (and ora-1555) are still introduced into the standby after switchover.  Setting _ktb_debug_flags=8 is mandatory before activating the standby: failure to do so results in permanent ora-1555 errors as was described above.

 

Posted in Uncategorized | 2 Comments

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