Monday 17 November 2014

RMAN Incremental Backups to Roll Forward a Physical Standby Database

* DataGuard setup between primary and standby databases - ORACLE 11gR2

* Some of the archive logs  in primary were deleted accidentally and these were noto shipped to Standby. We realized it after 3 days.

* We followed the approach mentioned in below link by just taking incremental backup in primary to rollforward standby database.

https://docs.oracle.com/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

Friday 14 November 2014

ORACLE 11gR2 - ASM Start failed with ORA-04031

This is 2 node RAC with ASM. 

ASM and DB was up and running in Node 1 where as ASM start in Node 2 (11gR2)failed with ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^f6c30305","kglHeapInitialize:temp").


DB is configured to use Automatic Memory Management feature.

Please see below doc for AMM feature.

I Double the value for memory_target, memory_max_target to get rid of error.

Logged in second node as 'grid' user and increased the value for both parameters and started ASM in Node 1.

 alter system set memory_max_target=4096m scope=spfile;
 alter system set memory_target=1024m scope=spfile;

As it was scope=spfile new value will reflect after next Instance restart in Node 2.

Saturday 4 October 2014

File Transfer failed with "File too large"

Most of you might be aware of this already.

I was trying to move files from ServerA to ServerB. After copying 10GB, file transfer failed with "File too Large".

Login Target server as root and put the following entry in /etc/security/limits for that particular user.

user1:
        fsize = -1
        nofiles = -1

This makes the size of file transfer as "unlimited".

Hope this helps.


Sunday 14 September 2014

Oracle Goldengate - ERROR: sending message to REPLICAT (Timeout waiting for message).

ORACLE 11gR2 - GoldenGate

Recently we observed  lag in one of the Replicat in Golden Gate. Tried to get 'stats','lag','stop' this replicat. However all these commands failed with 
"ERROR: sending message to REPLICAT <Replicat Name> (Timeout waiting for message)."

Identified Unix PID for this replicat and killed and restarted it. 
ps -ef| grep <replicat name>; kill -9 <pid>

This resolved the issue and we see laggap decreasing after this.



Friday 12 September 2014

ORA-01555 on Standby

DB - 11gR2

Users were running select queries on Primary where as the same select was failing with ORA-01555 in Active standby. 

Undo_retention =   900 sec --Primary
Undo_retention = 21600 sec --Standby

Inspite of higher retention at standby users were facing ORA-01555. 

Later realized that undo_retention at stantby does not have any significance. Oracle Dataguard just replicates the undo_retention value from Primary. Increased the Undo_retention in Primary to get rid of this error.

Below note  really helped in understanding this.
http://alexeymoseyev.wordpress.com/2013/10/24/ora-01555-on-standby/



ORA-12502: TNS:listener received no CONNECT_DATA from client.

Users were able to connect from One client where as they were having issues from another client.
Jobs were failing with ORA-12502 error.

This is RAC env. 11gR2

From both clients
1. we can telnet,ping to db hosnames
2. we can telnet,ping to scan

however telnet to VIP was not working for problematic client. Informed apps team to Open firewall request for VIPs from client. This resolved the issue.

Below two links helped me in troubleshooting the issue.

http://levipereira.wordpress.com/2011/05/03/configuring-client-to-use-scan-11-2-0/
http://stelliosdba.blogspot.sg/2012/02/ora-12502-tnslistener-received-no.html

Monday 26 May 2014

DB2 V9.7 Pending Free Pages

Pending free pages is a big headache in db2 v9.7. This is with automatic storage tablespaces.
Pending free pages does not release space back to File system.

Following query gives the top 25 tablespaces based on TBSP_PENDING_FREE_PAGES in desc order.

select char(TBSP_NAME,20) TBSP_NAME, sum(TBSP_USED_PAGES)TBSP_USED_PAGES, sum(TBSP_FREE_PAGES)TBSP_FREE_PAGES, sum(TBSP_TOTAL_PAGES)TBSP_TOTAL_PAGES, sum(TBSP_PAGE_TOP) TBSP_PAGE_TOP, sum(TBSP_PENDING_FREE_PAGES)TBSP_PENDING_FREE_PAGES FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t where TBSP_CONTENT_TYPE NOT IN  ('USRTEMP','SYSTEMP') group by tbsp_name order by  TBSP_PENDING_FREE_PAGES desc fetch first 25 rows only

So far, DB2 does not give any option which application is holding the pending free pages so that it can be killed.  Raised this concern with IBM labs.

So far I was successful in the following ways

1. take the back of tablespace
2. db2_all "db2 connect to <dbname> ;db2 list tablespaces show detail;db2 terminate;" > /dev/null
3. Sometimes stopping eventmonitors also help

Below two methods I use to verify if the extent movement is in lock wait status.

1. db2 "select * from sysibmadm.SNAPLOCKWAIT"
2.db2 list applications global show detail | grep <instanceName> | grep -v db2fw | grep -v db2bp | grep -i db2ExtMov 

Wednesday 7 May 2014

Saturday 19 April 2014

DB2 v9.7 - SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2

xx Apr 20xx 09:52:06,363 [quartzScheduler_Worker-3] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;THIS_.ISO_CNTRY_CD, DRIVER=3.50.152
xx Apr 20xx 09:52:06,363 [quartzScheduler_Worker-3] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;THIS_.ISO_CNTRY_CD, DRIVER=3.50.152


Users were facing this issue after deployment. Tired doing rebind package but it did not work.


Later realized that ISO_CNTRY_CD column does not exist in the table.  they changed the column name in the job and issue got resolved.

Tuesday 28 January 2014

Partition detach hanged in DB2 v9.7

I accidentally issued folloiwng command twice and it was showing partition detach status for a while for small table.

alter table T1 detach partition PART5 intoT1_02

db2 list utilities was showing AIC was running for 90 mins..

db2 list utilities show detail | grep -p -i detach
ID                               = 1208
Type                             = ASYNCHRONOUS PARTITION DETACH
Database Name                    = DB1
Partition Number                 = 0
Description                      = Finalize detach for partition '9' of table 'T1' and make table 'T1_02' available
Start Time                       = 01/28/2014 10:48:37.682113
State                            = Executing
Invocation Type                  = Automatic
Progress Monitoring:
      Description                = Waiting for old access to the partitioned table to complete.
      Start Time                 = 01/28/2014 13:08:40.480134

db2pd -util

Database Partition 0 -- Active -- Up 93 days 14:48:44 -- Date 2014-01-28-13.18.33.334414

Utilities:
Address            ID         Type                   State      Invoker    Priority   StartTime           DBName   NumPhases  CurPhase   Description
0x0780000001FAE4C0 1208       ASYNCHRONOUS PARTITION DETACH 1          1          0          Tue Jan 28 10:48:37 DB1  -1          -1          Finalize detach for partition '9' of table 'T1' and make table 'T1_02' available

Progress:
Address            ID         PhaseNum   CompletedWork                TotalWork                    StartTime           Description

I killed the hanging application as per the following link..
http://www-01.ibm.com/support/docview.wss?uid=swg21601085

Hope this helps..

Sunday 26 January 2014

Introduction to Unix LVM

the following link gives nice introduction to LVM aka logical volume manager

unix LVM

http://www.youtube.com/watch?v=BysRGDgqtwY

Friday 24 January 2014

DB2 V9.7 - db2fmpterm

This is new for me. Today i encountered high memory usage by fenced user id in V9.7 FP 6 running on AIX.
FMP process count is unusually high and contributing to high memory usage.

We restarted instance to fix the issue. However, meanwhile IBM suggested to use
" db2fmpterm <pid of offending> " to kill fmp process rather than recycling the instance.

Hope this helps.

Sunday 19 January 2014

DB2 V9.7 - Reduce Tablespace Size

I have got an opportunity to work with different versions of DB2 LUW environments.
This is what I follow if I need to reduce the size/hwm of tablespace.

For TBS migrated to V9.7 -with Automatic storage  - alter tablespace tbs reduce
For TBS migrated to V9.7 - without Automatic storage  - alter tablespace tbsname reduce (all 1000)   - 1000 is no.of pages
For TBS created in V9.7 - Automatic storage - alter tablespace tbsname reduce max
For TBS created in V9.7 - without Automatic storage - alter tablespace tbsname lower high water mark

Monitor the progress in V9.7 using below command
select varchar(tbsp_name, 15) as tbsp_name, tbsp_state from table (mon_get_tablespace('TBS1',-2)) as t;



Hope this helps.

Saturday 18 January 2014

DB2 V9.7 Audit setup - Quick Steps

These steps are from my Run notes. This may help one of you who is looking for quick audit set up.


db2audit is changed from V9.5.  From V9.5, audit is in two levels. One is at Instance level and another is at DB level.

This is how I configure for any new DB set up.

Instance level
---------------
db2audit configure reset
db2audit configure scope audit status BOTH, CHECKING STATUS FAILURE, OBJMAINT STATUS BOTH, SECMAINT STATUS BOTH, SYSADMIN STATUS FAILURE, VALIDATE STATUS BOTH,CONTEXT STATUS NONE datapath /auditdata/ archivepath /auditarch/
db2audit start

Verify Instance level setting with below command
db2audit describe

DB Level
-----------
db2 "connect to <dbname>"
db2 "CREATE AUDIT POLICY CHECK_AUDIT CATEGORIES   VALIDATE STATUS BOTH,  CHECKING STATUS NONE, OBJMAINT STATUS BOTH, SECMAINT STATUS BOTH, CONTEXT  STATUS NONE, AUDIT    STATUS BOTH,  SYSADMIN STATUS BOTH   ERROR    TYPE   AUDIT"
db2 "AUDIT DATABASE USING POLICY CHECK_AUDIT"

Verify DB  level setting with below command
db2 "select substr(AUDITPOLICYNAME,1,25) policy,OBJECTTYPE,SUBOBJECTTYPE,substr(OBJECTSCHEMA,1,20) schema,substr(OBJECTNAME,1,20) object from syscat.audituse"

db2 "select substr(AUDITPOLICYNAME,1,10) policy,AUDITSTATUS,CONTEXTSTATUS,VALIDATESTATUS,CHECKINGSTATUS,SECMAINTSTATUS,OBJMAINTSTATUS,SYSADMINSTATUS,EXECUTESTATUS,EXECUTEWITHDATA from syscat.auditpolicies"

Hope this helps...

DB2 LUW - Online Reorg


In DB2 LUW, Online reorg of table is Asynchronous which means if you trigger the command DB2 immediately gives message like "completed successfully" but it runs in the background.
Lets say you have a set of tables and would like to perform Online reorg on them using a script.
If you trigger a script it gets completed in few seconds however, DB2 runs them in the back ground (Read Online reorg of a table is Asynchronous). System may become IO bound.

Following function checks if online reorg on a table is completed and if it is, then reorg for next table gets triggered.

sysproc.snapshot_tbreorg - Table function being used
Reorg_status = 4 <if online reorg is completed>

online_reorg()
{
    unset PID
    echo "."
    echo "###Online Reorg started on ${TBNAME} at `date`"
    db2 -v "reorg table ${SCHEMA}.${TBNAME} inplace allow write access" &
     sleep 1
    unset CHK

    while true;do
        CHK=`db2 -x "select REORG_STATUS from table(sysproc.snapshot_tbreorg('',-1))as t where table_schema in '${SCHEMA}' and table_name in '${TBNAME}'"`
        if [ $CHK = 4 ];then
           echo "###Online Reorg completed on ${TBNAME} at `date`"
           break
        else
           sleep 2
        fi
    done
   }


Hope this function helps you...

Thanks for reading.

Received "Error (-3) initializing encryption context: Encryption method not implemented" while setting up GoldenGate..



I received following error while setting up encryption for password in GoldenGate.

GGSCI  1> encrypt password y1234 AES128 EncryptKey key1
Password encryption failed...
Error (-3) initializing encryption context: Encryption method not implemented.

Not able to find solution for this. Hence did the following work around....

GGSCI 8> encrypt password y1234  ENCRYPTKEY DEFAULT
Using default key...
Encrypted password:  AACAXXXXXAAWSXTGBG
Algorithm used:  BLOWFISH


Hope this helps..

Thursday 16 January 2014

DB2 V9.7 How to calculate Database Size

Most of the time " GET_DBSIZE_INFO" should suffice in getting DB size. However, sometimes DB2 gives '-1' as it is shown below (don't know why it is ).

db2 "CALL GET_DBSIZE_INFO(?, ?, ?, -1) "

  Value of output parameters
  --------------------------
  Parameter Name  : SNAPSHOTTIMESTAMP
  Parameter Value : 2014-01-16-09.22.03.122236

  Parameter Name  : DATABASESIZE
  Parameter Value : 122732959662080

  Parameter Name  : DATABASECAPACITY
  Parameter Value : -1

  Return Status = 0

Recently, I started using below SQL.

db2 "select sum(TBSP_TOTAL_SIZE_KB)/1024/1024 allocated_GB, sum(TBSP_USED_SIZE_KB)/1024/1024 Used_GB, 
sum(TBSP_FREE_SIZE_KB)/1024/1024 free_GB 
from sysibmadm.TBSP_UTILIZATION with ur"

DB2 V9.7 Last successful backup timestamp

I support  DB2 LUW V9.7 databases.
I used to check last successful backup using "list history" and check for successful one.
One of my colleague shared below SQL which gives result very fast as it makes use of table function

Last successful DB backup
db2 "select SQLM_ELM_LAST_BACKUP from table(SNAPSHOT_DATABASE( cast( null as VARCHAR(255)), cast(null as int))) as ref"

Friday 10 January 2014

Today I accidentally deleted all my mails in outlook 2010. I was able to recover all mails with the help of below link.

http://office.microsoft.com/en-us/outlook-help/restore-deleted-items-in-outlook-HA010355039.aspx

Thursday 2 January 2014

Singapore - Jurang Bird park - My itinerary

Recently visited Jurang bird park with my parents, spouse and kid.  As there are many exhibits, we have to plan in advance or else one may miss the free shows available there.

Visited this place On Jan 01, 2014. Weather was pleasant  Though the weather was nice, we end up drinking 12-15 liters of water (Humid). I would suggest to keep a water bottle with you always.

As we are 5 in total (4 adults and my 2.5 years old), we hired taxi (from Eunos ) to reach Jurang BP.

Here is what I followed.
10-10.20 am - King of Skies (show)






10.30-10.45 am - Penguin coast (show)







10.45-11 am - Flamingo



11-11.30 am - high flyers (show)









11.30 to12.30pm - on the way to Dinosaur descendants, we covered helicans, hornbills.



Mother managed to get colored feathers of helicans



12.30- 1.35pm - Lorry loft, jungle jewels, bird discovery center and completed our lunch. This is home packed lunch. My parents likes the lorry loft where we can feed parrots. I think it is a kind of soup which one can purchase by paying SGD 3 at the counter.


 we happened to meet love birds
 my 2.5Y old was running all over the hanging bridge


 This was the best :). All parrots were doing massage to me. See the person behind me, he was trying to get attention of one of these parrots and he succeeded at the end :)



1.40pm - 1.50pm - ostrich feed (kind of show)

These the various sizes of eggs of different brids
 I believe This is of Ostrich's


2pm -2.20pm - pelicon cove (show)





2.30pm to 2.45pm - then we moved slowly to water fall aviary and happened to see bird's feeding.




Water falls pic:

3pm to 3.30pm - exploring different parrots - However, you do not see this in the official bird's park map.
It is diagonally opposite to Shoe bill on the left.

We managed to spot parrots/Macau with different colors.

This time it is pinkish white

we also manage to see some parrots having a quick nap
 parrots having fun on a rope
 Blue bird
 Sexy black pair
 Father was curious about this...well Do not know about this...
 with all vivid colors
 white one this time..
 Pink+dull white


 Macau :)



 3.45pm-5pm - Kids was enjoying int he play area while we were having yummy ben & Jerry Ice creams.



On the way back, we covered shoe bill and flamingos again (this timethey are white in color)
 white Flamingo this time..


 Shoe bill
Thanks for reading.