unaccounted time in v$sql elapsed_time >> cpu_time + user_io_wait_time + concurrency_wait_time + etc

Regarding unaccounted time in v$sql, when elapsed_time is much bigger than cpu_time + user_io_wait_time + concurrency_wait_time + etc.

One reason: Log writer waits are not included into user_io_wait_time – it is truly a USER io time.

Test it: Stop log writer: kill -s SIGSTOP <lgwr_pid>. See how elapsed_time increases but other times stay unchanged.

Another credible reason: OS is losing time on paging (swapping)

 

 

Posted in Uncategorized | Leave a comment

Recovery, incomplete, consistency, datafile headers, checking, minimum SCN

x$kcvfh is a good one:

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)

select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;

Posted in Uncategorized | Leave a comment

Diag and Tuning Pack: disable

Disable Diag and Tuning pack (oracle 10g and 11g)

- Baselines must not be used:
begin
for m in (select * from dba_hist_baseline where baseline_name != ‘SYSTEM_MOVING_WINDOW’) loop
sys.dbms_workload_repository.drop_baseline(baseline_name=>m.baseline_name,cascade=>true);
end loop;
end ;
/

- Manual Snapshots must not be used:
begin
for m in (select * from dba_hist_snapshot where snap_flag0) loop
sys.dbms_workload_repository.drop_snapshot_range(low_snap_id=>m.snap_id,high_snap_id=>m.snap_id);
end loop;
end ;
/

- awr disable
begin sys.dbms_awr.disable_awr(); end;
/

- Disable Advisors

- 11g: tuning schedulers disabled. That should be optional as far as licenses are concerned but there are many reasons to get rid of them.

- OEM (Cloud Control, formerly Grid Control, formerly OEM): disable Packs in Grid Control interface, menu on the top-right corner.

Next: mapping between licensed features and dba_feature_usage_statistics is known – see metalink id: Database Options/Management Packs Usage Reporting for Oracle Database 11g Release 2 (Doc ID 1317265.1)

Then one can do this:

1) map features to dba_feature_usage_statistics and sys.wri$_dbu_feature_metadata

2) sys.wri$_dbu_feature_metadata describes how oracle checks for feature usage

3) the feature is disabled based on analysis of sys.wri$_dbu_feature_metadata

4) Mos article “MMON Suspended Due to ORA-12751 “cpu time or run time policy violation” (Doc ID 761298.1)” is used to execute oracle FUS immediately:
alter session set events ‘immediate trace name mmon_test level 6′;

5) repeat from step 2 untill dba_feature_usage_statistics is satisfactory

 

Posted in Uncategorized | Leave a comment

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