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;

Size your Undo Tablespace

Size your Undo tablespace


What should be the size of UNDO tablespace? Generally this question is vaguely answered saying that sizing comes with experience (of DBA) or looking at load on server or sometimes even by the number of ORA-1555 or out of space errors.



This paper is to help DBA’s in calculating the size of UNDO tablespace by using a simple formula.



While designing an application, generally it is tough to know about the number of transactions and subsequently number of rows changed per second.

So I suggest having a “big undo tablespace” to start with and based on load, after doing some calculations and resize your UNDO tablespace.



In my case one of the applications was going to production (live), and I had no idea that how many transactions will happen against this database. All what I was told that there will be optimum (transactional) activity on this database. The word “optimum” itself is vague.



So I started with UNDO tablespace with size of 2GB and datafiles with autoextend “on” .



Note:

In production, you must be very careful in using this (autoextend on) as the space may grow to inifinity very fast. So my advice is either dont use this option, or use with "maxsize" or continuously monitor space (which is tough).



I month later, I noticed the activity from V$undostat.



Here is the step by step approach:



Step 1: Longest running query.



SQL> select max(maxquerylen) from v$undostat;





MAX(MAXQUERYLEN)

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

1793



This gives you ideal value for UNDO_RETENTION. To be on the safer size you should add few more seconds to get the right value. So in my case, the size of undo retention should be say 2000 secs.



Step 2: Size of UNDO tablespace.



Size of UNDO needed = UNDO_RETENTION x [UNDO block Generation per sec x DB_BLOCK_SIZE] + Overhead(30xDB_BLOCK_SIZE)



Out of these we know UNDO_RETENTION and DB_BLOCK_SIZE



All we need is to find out “UNDO Blocks per second”



Which can be easily fetched from v$undostat



SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UPS"

2 FROM v$undostat;





UPS

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

8.11985583



V$undostat stores data for every 10 mins and begin/end times are start/end time of those intervals. We multiplied it with 24*60*60 because the difference between two dates will be in days and to get to seconds, we need it to multiply with 24hrs*60mins*60secs



So now we have all the values needed.



Undo size needed = [8.12 x 2000 x 8192] + [30 x 8192] = 133283840 bytes = 127.11 MB