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.

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...

Wednesday, November 2, 2011

ORA-00257 archiver error

User reported that they are getting ORA-00257 error, when they trying to access the production database.
ORA-00257 commonly related to Archiver not able to write archive logs into File system/ asm disk.
After checking, I came to find out it is caused by Flash Recovery area run out space. How did I find out
SQL> select space_limit,space_used from v$recovery_file_dest;
SPACE_LIMIT SPACE_USED
-------------- ----------------
10737418240 10737418240
I cleaned up the old archive logs using the famous RMAN command after backing up,
RMAN> crosscheck archivelog all;
Found some old archive logs, which is already backup was there in the Flash recovery area. I used the following command to delete archivelog older than 7 days.
RMAN> delete archivelog completed before 'sysdate -7';
Then users can continue with their work. Another option is, if you have space on the disk, on the fly you can increase the db_recovery_file_dest_size with addition space
sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size=20G scope=both;
System altered.

Why simple Oracle DBA

On a day to day activity of a Oracle DBA, you will encounter lot of small and large challenging work to resolve. This blog will record all solutions implemented and tested on production, development and test environments Databases.