I am a simple Senior Oracle DBA Consultant....

Oracle Certified (Database 9i/10g/11g
& Application 11i), dynamic, self-motivated and results-oriented
professional with over 16 years of broad- based experience in Database
Management, Administration, Backup & Recovery, Tuning and Oracle
Application Concepts and Architecture using Oracle Technologies (Database,
Oracle Application and Middleware). Expertise in high-technology time and cost
based solutions, quality customer service and commitment to excellence.

Monday, September 9, 2013

dbms_stats.gather_table_stats(....degree=>6....,cascade=>true)

Hi all,

Today I was given with a question, why a dbms_stats with degree=>6 created 12 parallel process instead of 6 process.

I was wondering, how come Oracle create 12 process where as degree is only 6 process.  I think many of you might know already.

Oracle creates 6 parallel process as requested, but because of "cascade=>true", it spawns another 6 process to collect statistics from Index.

Oracle uses the following hint to run the stats collect process.

/*+ PARALLEL(t, 6) PARALLEL_INDEX(t,6) */


Thursday, July 11, 2013

ORA-00001: unique constraint(schema.table_name) violated

I faced an error from a production ERP system, which the trace file shows "Unique constraint violated".  Actually this process inserts rows from temporary table to target table.  The temporary table has got 1000 of rows.  Easy way to check the error,

1) check which columns are part of the unique constraints

    select constraint_name,constraint_type,search_condition from dba_constraints where table_name=<TABLE_NAME>;

In my case the target table has got check constraints, so can't find it in dba_constraints.

2) check unique index for the target table

    select index_name,uniqueness from dba_indexes where table_name=<TABLE_NAME>;
    select column_name,column_position from dba_ind_columns where index_name=<PREVIOUS STEP VALUE>;

3) check target table rows compare with temporary table rows for the specific unique index columns.

4) Correct the data.

Monday, June 24, 2013

Modify table with few additional columns with not null constraint and default value

There was a new requirement to add few columns to a big table (few gigs in size) with not null constraints and default value.

The developer came up with a script and asked me to review,

alter table T1 add F1 varchar2(10);
alter table T1 add F2 varchar2(10);
alter table T1 add F3 varchar2(10);

update T1 set F1=' ',F2=' ',F3=' ';


alter table T1 modify F1 not null;
alter table T1 modify F2 not null;
alter table T1 modify F3 not null;



Oracle has fantastic feature to do all the above steps in one go ....:()   Here you go....

alter table T1 add (F1 varchar2(10) default ' ' not null, F2 varchar2(10) default ' ' not null, F3 varchar2(10) default ' ' not null);

The above DDL ran faster and easy to manage it.

How to move a index from one tablespace to another

How to move index from tablespace to another tablespace online.

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
SYS                                  TEST_SC                               OLD_INDEX


I want to move TEST_SC which is wrongly created in OLD_INDEX tablespace.  User want to move to NEW_INDEX tablespace,

SQL>alter index <INDEX_NAME> rebuild tablespace <NEW TABLESPACE> PARALLEL NOLOGGING;

SQL>alter index TEST_SC rebuild tablespace NEW_INDEX PARALLEL;

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
SYS                                  TEST_SC                               NEW_INDEX

User carefully with NOLOGGING option if you have DR. 

If you are moving large index, check in V$SESSION_LONGOPS to see how much it progressed and how long it will take (approx).



Thursday, December 1, 2011

Interesting in GoldenGate, I have requirement from a client to store the source transaction ROWID in target table. I have been through the documentation and reference guides, end up not finding anything interesting and stright forward. But managed to find the solution and here you go and enjoy,
It is managed by GG tokens, those who don't know GG, please refer the best Oracle Documentation
I have created the source extract parameter file with
TKN-ROW_ID = @GETENV("RECORD", "rowid")
and created my traget replicat process parameter file with
ROW_ID = @TOKEN("TKN-ROW_ID"))
It did the magic and I could see the target records as follows,
SCHEMA ROW_ID EMP_ID-------------------- ------------------------------ ----------SRC AACZoBAAXAAAACVAAF 550001334SRC AACZoBAAXAAAACVAAG 440001334SRC AACZoBAAXAAAACVAAH 2220001334SRC AACZoBAAXAAAACWAAA 990001334SRC AACZoBAAXAAAACWAAB 100001334SRC AACZoBAAXAAAACWAAC 70001334
Enojoy.....if you find this update useful....leave your comment.....

Tuesday, November 8, 2011

RAC instance startup failed with : ORA-27154: post/wait create failed

Today, one of the client database was down. Trying to start with
$ srvctl start instance -d TEST -i TEST3
PRCR-1013 : Failed to start resource ora.test.db
PRCR-1064 : Failed to start resource ora.test.db on node test-rac03
CRS-5017: The resource action "ora.test.db start" encountered the following
error:ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
CRS-2674: Start of 'ora.test.db' on 'test-rac03' failed
Tried through SQL plus,
SQL> startupORA-27154: post/wait create failedORA-27300: OS system dependent operation:semget failed with status: 28ORA-27301: OS failure message: No space left on deviceORA-27302: failure occurred at: sskgpcreates
checked in trace fileStarting ORACLE instance (normal)Errors in file /u01/app/oracle/TEST/diag/rdbms/test/TEST3/trace/TEST3_ora_10781.trc:ORA-27154: post/wait create failedORA-27300: OS system dependent operation:semget failed with status: 28ORA-27301: OS failure message: No space left on deviceORA-27302: failure occurred at: sskgpcreatesWed Nov 09 15:57:53 2011
From the trace filedbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)----- Error Stack Dump -----
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
$sudo su -
# /sbin/sysctl -a | grep semkernel.sem = 250 32000 100 142
Increased the semophores value to 180
This is a linux host, so I changed the value and re-initialize immediately.
cd /etc
vi sysctl.conf
look for kernel.sem
change the last value from 140 to 180
[root@npe-ora-rac03 etc]# /sbin/sysctl -p
net.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 1net.ipv4.conf.default.accept_source_route = 0kernel.sysrq = 1kernel.core_uses_pid = 1net.ipv4.tcp_syncookies = 1kernel.msgmnb = 65536kernel.msgmax = 8192kernel.shmmax = 4398046511104kernel.shmall = 1073741824fs.file-max = 6815744kernel.msgmni = 2878kernel.sem = 250 32000 100 180kernel.shmmni = 4096net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576fs.aio-max-nr = 3145728net.ipv4.ip_local_port_range = 9000 65500vm.min_free_kbytes = 51200
srvctl start instance -d TEST -i TEST3
**Issue resolved**

Thursday, November 3, 2011

Oracle Streams ORA-01403: no data found...

Today one of our client reporting database has got 73 - ORA-01403: no data found errors. And their Business objects reports were out-of-sync.
I will explain here, how I resolved it using packages and package bodies....very interesting...