Friday, November 27, 2009

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

As Oracle documentation states that "none of the columns in the unique/primary key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique/primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE".

So if you have a table that has a TIMESTAMP WITH TIME ZONE column this column cant have or be part of a unique/primary key constraint. In order to bypass this you can create a unique index including this column.

e.g

create table test (a int primary key,b int, c timestamp with time zone);

alter table test add constraint test_u01 unique (b,c);

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

create unique index test_u01 on test(b,c);

Index created !!!

Thursday, November 26, 2009

Date and time arithmetics with interval

-- Add five years to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' year FROM dual;

-- Add five months to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' month
FROM dual;

-- Add five days to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' DAY
FROM dual;

-- Add five hours to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5' hour
FROM dual;

-- Add five hours and 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '5:10' hour to minute
FROM dual;

-- Add 10 minutes to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10' MINUTE
FROM dual;

-- Add 10 minutes and 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;

-- Add 30 seconds to current_timestamp
SELECT current_timestamp,current_timestamp + INTERVAL '30' SECOND
FROM dual;

Wednesday, November 25, 2009

Oracle 10g flashback table feature

FLASHBACK TABLE enables to recover a table to a point in time in the past without restoring a backup.The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.You cannot restore a table to an earlier state across any DDL operations that change the structure of the table.You cannot roll back a FLASHBACK TABLE statement but you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Its crucial to record the current SCN before issuing a FLASHBACK TABLE clause.

In order a user to flash back a table to an earlier SCN or timestamp, or before drop you must have either :
1.FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege.
2. SELECT, INSERT, DELETE, and ALTER object privileges on the table.
3.Row movement must for tables in the Flashback list.

e.g
create table test (a int);
alter table test enable row movement;
insert into test values(1);
insert into test values(2);
insert into test values(3);
commit;


select dbms_flashback.get_system_change_number from dual;

8657840224 -- SCN before delete

delete from test where a=3;

commit;

select dbms_flashback.get_system_change_number from dual;

8657840556 -- SCN after delete

select * from test;
1
2

flashback table test to scn 8657840224; -- Restore table to before delete !!!

select * from test;

1
2
3

flashback table test to scn 8657840556; -- Restore table to after delete !!!

select * from test;

1
2

flashback table test to timestamp(systimestamp - interval '3' minute);

select * from test;

1
2
3

Now we will drop the table and flashback from recycle bin.

drop table test;

flashback table test to before drop;

select * from test;

1
2

And as you can see we can go back again !!!

flashback table test to scn 8657974029;

select * from test;

1
2
3

If you specify the user-specified name, and if the recycle bin contains more
than one object of that name, then the database retrieves the object that
was moved to the recycle bin most recently.

drop table test;


create table test (a int);
alter table test enable row movement;
insert into test values(1);
commit;

select * from test;
1

drop table test;

flashback table test to before drop;

select * from test;
1

flashback table test to before drop rename to test1;

select * from test1;
1
2
3


Now lets take a look information recycle bin has.

drop table test1;

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

owner object_name original_name operation can_undrop can_purge
--------------------------------------------------------------------
AGIS BIN$eTA7dNdz5FfgQAkKeIc6fw==$0 TEST1 DROP YES YES


purge recyclebin; -- Clear recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

flashback table test1 to before drop;

ORA-38305 Object not in RECYCLE BIN.

drop table test; -- Put table to recycle bin

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


OWNER OBJECT_NAME ORIGINAL_NAME OPERATION CAN CAN
------------------------------ ------------------------------ -------------------------------- --------- --- ---
AGIS BIN$eTA7dNd05FfgQAkKeIc6fw==$0 TEST DROP YES YES

flashback table test to before drop;


select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';

no rows selected

drop table test purge; -- Permanently drop table

select owner,object_name,original_name,operation, can_undrop, can_purge
from dba_recyclebin
where owner='AGIS';


no rows selected


Amazing 10g recovering features with no backup recover !!!!!

Tuesday, November 24, 2009

Reorginize table online using primary key

From Oracle9i forward, table re-organizations and redefinitions can be performed online.

What can be Redefined ONLINE on a Table?

1) A non-partitioned table can be converted into a partitioned table, and
vice versa
2) The organization of a table can be changed from a heap based to IOTs and vice versa
3) Non-primary key columns can be dropped,although this can be also done with sql
4) New columns can be added to a table,although this can be also done with sql
5) Existing columns can be renamed,although this can be also done with sql
6) Parallel support can be added or removed,although this can be also done with sql
7) Storage parameters can be modified,although this can be also done with sql

Restrictions

The table to be re-organized:

1) Cannot have User-defined data types
2) Cannot have FILE or LONG columns
3) Cannot be clustered
4) Cannot be in the SYS or SYSTEM schema
5) Cannot have materialized view logs and/or materialized views defined on them
6) Must be re-organized within the same schema


Example

Orig table : Table with hash 256 partitions , with pk , unique index , and fk
Target table : Non partitioned table

Connect as user with the privileges mentioned above.


1) Check if ORIG_TABLE can be redifined online
exec DBMS_REDEFINITION.can_redef_table('MYSCHEMA','ORIG_TABLE');
If procedure fails check ora error and apply solution if possible.

2) Create interim table

create table I_ORIG_TABLE as select * from ORIG_TABLE where 1=2;

3) Start online redefinition

exec DBMS_REDEFINITION.START_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

4) Add constraints and indexes

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT I_ORIG_TABLE_PK
PRIMARY KEY
(PK_COLUMN));

ALTER TABLE MYSCHEMA.i_ORIG_TABLE ADD (
CONSTRAINT i_ORIG_TABLE_FK
FOREIGN KEY (REF_COL)
REFERENCES MYSCHEMA.OTHER_TABLE (PK_COL)
DEFERRABLE INITIALLY IMMEDIATE);

alter table i_ORIG_TABLE MODIFY CONSTRAINT i_ORIG_TABLE_FK
DISABLE KEEP INDEX;

CREATE UNIQUE INDEX MYSCHEMA.i_ORIG_TABLE_IDX01 ON MYSCHEMA.ORIG_TABLE
(COL1, COL2);

5) Sync interim with original table

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

Do this a lot of times before finish redefinition in order to apply dml during reorganization.

6) Find orig_table grants

select * from DBA_TAB_PRIVS where table_name='ORIG_TABLE';

Give the same grants to the users

e.g

grant select on I_ORIG_TABLE to user;

7) Finish redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYSCHEMA','ORIG_TABLE','I_ORIG_TABLE');

8) Drop old table and rename constraints and indexes.

drop table I_ORIG_TABLE cascade constraints purge; -- At this point old ORIG_TABLE has been renamed to interim table name.

alter table ORIG_TABLE rename constraint I_ORIG_TABLE_PK to ORIG_TABLE_PK;

alter index I_ORIG_TABLE_PK rename to ORIG_TABLE_PK;

alter table ORIG_TABLE rename constraint i_ORIG_TABLE_FK to ORIG_TABLE_FK;

alter index I_ORIG_TABLE_IDX01 rename to ORIG_TABLE_IDX01;

9) Gather statistics if needed

begin
dbms_stats.gather_table_stats(ownname=>'MYSCHEMA',tabname=>'ORIG_TABLE',cascade=>true,estimate_percent=>5);
end;
/

Now you have an new unpartitioned table with all constraints and indexes and without partitions.

Tuesday, November 17, 2009

ORA-27477 Creating a scheduler job.

When the name of the job is the same with an object name in the same schema of the creator of the job you get the ORA-27477 %s.%s already exists error in the EM job page.

Change the job name in order not to match an existing schema object name.

Thursday, November 12, 2009

Oracle row migration and chaining

A very nice article about row migration and chaining.

http://www.akadia.com/services/ora_chained_rows.html

Wednesday, November 11, 2009

enq: TX - allocate ITL

When you see this enqueue wait event in the dbconsole performance page or in the top 5 timed waits event seems that too many concurrent DML transactions are competing for the same data block.

Usually an increase in the value of initrans or maxtrans for the table and the indexes fixes the problem.

You can also try to move the table to a tablespace with smaller block size and reduce the parallel degree of the table and indexes.

Oracle memory usage AIX

The following link contains a collection of scripts that calculate oracle memory usage on AIX.

mem.zip :  https://drive.google.com/file/d/0B8hdAvkza6deal9SeFBzWDg2MkU/edit?usp=sharing


The file mem.rar contains:

instance_mem_use.sh
This script is used to measure memory usage of oracle instance asm instance , background processes , dedicated processes and also dbconsole processes.

Usage: instance_mem_use.sh dbsid dbname

In most cases dbsid=dbname but if you have a RAC node the dbsid differs from dbname.

Sample output :

/home/oracle $./instance_mem_use.sh RMINTPRD2 RMINTPRD
Memory Analysis for RMINTPRD2

Oracle Instance Memory
-----------------------------------------------------
System Global Area Memory Usage :5008MB
Program Global Area Memory Usage :500MB
-----------------------------------------------------

Oracle Processes Memory
-----------------------------------------------------
ORACLE Background Processes : 29
Memory Usage : 435MB

ORACLE Dedicated Processes : 154
Memory Usage : 1318MB

ORACLE dbconsole Processes : 1
Memory Usage : 5MB
-----------------------------------------------------


#####################################################
RMINTPRD2 Total Memory Usage :7268MB
#####################################################

other_mem_use.sh

This script is used to measure LISTENER,RMAN or CRS processes.

Usage: other_mem_use.sh LISTENER or CRS or RMAN

Sample otuput :

/home/oracle $ ./other_mem_use.sh CRS

-----------------------------------------------------
ORACLE CRS Processes : 18
CRS Total Memory Usage :202MB
-----------------------------------------------------

These scripts are used for the caclucations.

mem.awk
mem_calc.awk



References

Metalink note 123754.1 : AIX: Determining Oracle memory usage on AIX

Tuesday, November 10, 2009

Oracle on AIX Memory Issues

When using Oracle on AIX and while time passes database gets slower you must look the amount of memory that AIX uses for filesystem cache. This is not the fact if you user raw devices or filesystem accessed by CIO or DIO.

Examples from AIX 5.3

As root :

#svmon -G

svmon -G
size inuse free pin virtual
memory 4096000 3483045 612955 426604 3312529
pg space 2064384 62882

work pers clnt
pin 426507 0 97
in use 3306854 22 176169

PageSize PoolSize inuse pgsp pin virtual
s 4 KB - 1297893 62882 361900 1127377
m 64 KB - 136572 0 4044 136572


Here the memory used for filessystem cache is : ((176169+22)/4096000)*100 = 4%

My settings on this system are :

maxperm% = 10
minperm% = 5
strict_maxperm = 1
maxclient% = 10
strict_maxclient = 1
lru_file_repage = 0
v_pinshm = 0
maxpin% = 80

And LOCK_SGA set to false.

These settings was proposed by sysadmin having experience on AIX 5.3.
Although i was recommend to use v_pinshm=1 and LOCK_SGA=true we are testing the previous settings and we are having no problems.

So my recomendation is to start with the settings i post in discuss with your sysadmin and if you do not have the optimal performance try v_pinshm=1 and LOCK_SGA=true.

Server configuration :

3 Node RAC using shared RAW Devices and ASM plus GPFS and NFS for backup.
Each server has 16Gb of RAM and 2 RAC database instances each.

You can also check the following IBM resources.

VMM Tuning Tip: Protecting Computational Memory
http://www.ibm.com/developerworks/wikis/download/attachments/53871915/VMM+Tuning+Tip+-+Proctecting+Comp+Memory.pdf?version=2

AIX 5.3 Peformance Mangement Guide
http://publib.boulder.ibm.com/infocenter/pseries/v5r3/topic/com.ibm.aix.prftungd/doc/prftungd/prftungd.pdf

Configuring IBM General Parallel File System (GPFS) with Oracle RAC http://www.ibm.com/servers/enable/site/peducation/wp/zb726/zb726.pdf

Optimizing AIX 5L performance: Tuning your memory settings
http://www.ibm.com/developerworks/views/aix/libraryview.jsp?search_by=Tuning+your+memory+settings

Support for pinned memory
http://publib.boulder.ibm.com/infocenter/systems/index.jsp?topic=/com.ibm.aix.prftungd/doc/prftungd/support_pinned_mem.htm