How to disable flush of ASH data to AWR?

MMON process will periodically flush ASH data into AWR tables.


Oracle introduced WF enqueue which is used to serialize the flushing of snapshots.



If for any reason ( space issue, bugs, hanging etc..) you need to disable flushing the run time statistics for

particular table than following procedure needs to be done.



First, locate the exact AWR Table Info (KEW layer):



SQL> select table_id_kewrtb, table_name_kewrtb from x$kewrtb order by 1



TABLE_ID_KEWRTB TABLE_NAME_KEWRTB

————— —————————————————————-

0 WRM$_DATABASE_INSTANCE

1 WRM$_SNAPSHOT

2 WRM$_BASELINE

3 WRM$_WR_CONTROL



—-



TABLE_ID_KEWRTB TABLE_NAME_KEWRTB

————— —————————————————————-

99 WRH$_RSRC_PLAN

100 WRM$_BASELINE_DETAILS

101 WRM$_BASELINE_TEMPLATE

102 WRH$_CLUSTER_INTERCON

103 WRH$_MEM_DYNAMIC_COMP

104 WRH$_IC_CLIENT_STATS

105 WRH$_IC_DEVICE_STATS

106 WRH$_INTERCONNECT_PINGS



107 rows selected.



1st option :



SQL> alter system set “_awr_disabled_flush_tables”=’’;



e.g.



alter system set “_awr_disabled_flush_tables”=’WRH$_INTERCONNECT_PINGS,WRH$_RSRC_PLAN’;



System altered.



2nd option:



SQL> alter session set events ‘immediate trace name awr_flush_table_off level 106′;

SQL> alter session set events ‘immediate trace name awr_flush_table_off level 99′



If you decide to turn on flushing statistics than



SQL> alter session set events ‘immediate trace name awr_flush_table_on level 106′;

SQL> alter session set events ‘immediate trace name awr_flush_table_on level 99′;

Network Time Protocol ( NTP ) & Clusterware diagnostic script

One of the prerequisites to successfully install Oracle version 11.2 is to set Network Time Protocol ( NTP )




( file /etc/sysconfig/ntpd )



with -x flag which prevents time from adjusting backward.



This comes very crucial in debugging Oracle Clusterware.NTP will synchronize clocks among all nodes which will make correct analysis of trace files based on time stamps .



Oracle is providing diagnostic collection script diagcollection.pl to collect important log files.



Script is located under $/bin/ e.g. /u01/app/11.2.0/grid/bin



It will generate four tar.gz files in local directory which will have following information:



traces,logs and cores for CRS home



ocrcheck , ocrdump



CRS core files



OS logs



After you are done you can clean them with same script.Just run diagcollection.pl -clean option.



Script must be run as root .

Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.




SQL> create table t2 (id number(10), t varchar2(20));



Table created.





SQL> insert into t2 values (1,'A');



1 row created.



SQL> insert into t2 values (1,'A');



1 row created.



SQL> insert into t2 values (1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;

alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE

*

ERROR at line 1:

ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found





SQL> select * from t2;



ID T

---------- ------------------------------------------------------------

1 A

1 A

1 A



So normal method does not work !







Case 1: New Table That means initially the table does not have any data



SQL> create table t3 (id number(10), t varchar2(20));



Table created.



SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;



Table altered.



SQL> alter table t3 disable constraint gpu;



Table altered.





SQL> insert into t3 values(1,'A');



1 row created.



SQL> insert into t3 values(1,'A');



1 row created.



SQL> insert into t3 values(1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL>

SQL>

SQL>

SQL> select * from t3;



ID T

---------- ------------------------------------------------------------

1 A

1 A

1 A







SQL> alter table t3 enable novalidate constraint gpu;



Table altered.



SQL> insert into t3 values(2,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> insert into t3 values(2,'A');



1 row created.



SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (HASAN.GPU) violated



SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;



Table altered.



SQL> insert into t3 values(1,'A');

insert into t3 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.GPU) violated







Case 2: Existing Table that contains duplicate data





SQL> create table t2 (id number(1),a varchar2(10));



Table created.





SQL> insert into t2 values(1,'A');



1 row created.



SQL> insert into t2 values(1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;



Table altered.



SQL> alter table t2 enable novalidate constraint uk_t2;



Table altered.



SQL> insert into t2 values(1,'A');



1 row created.



SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (HASAN.UK_T2) violated





SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;



Table altered.



SQL> insert into t2 values(1,'A');

insert into t2 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.UK_T2) violated





OR





SQL> alter table t2 add constraint uk_t2 unique(id) disable;



Table altered.





SQL> alter table t2 enable novalidate constraint uk_t2;



Table altered.



SQL> insert into t2 values(1,'A');

insert into t2 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.UK_T2) violated

Estimate Tablespace growth

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :




SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days

, ts.tsname

, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB

, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB

FROM DBA_HIST_TBSPC_SPACE_USAGE tsu

, DBA_HIST_TABLESPACE_STAT ts

, DBA_HIST_SNAPSHOT sp

, DBA_TABLESPACES dt

WHERE tsu.tablespace_id= ts.ts#

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name

AND ts.tsname NOT IN ('SYSAUX','SYSTEM')

GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname

ORDER BY ts.tsname, days;