AskDba.org Weblog

Recovering from Lost AWS EC2 Key

This article is for AWS users using EC2 instances. Summary: Please backup your AWS EC2 Key file and you will never have to go through below pain

AWS allows you to create a key-pair which can be downloaded on your machine and it is used for logging into your EC2 instance. I had been using the same for accessing the EC2 instance for this blog. But last week my corporate laptop was upgraded and I took backup of all files except this key file.

I didnt realize this until saturday when I wanted to login to my EC2 instance. When I couldn’t find the file, I thought of downloading it again from AWS console. But it was no where to be found. I searched and found out that we can’t download this file again.

Only solution to this problem is to create new EC2 instance with new key pair and then move site . [Read More]

cursor: pin S wait on X

Few weeks back thread came up on oracle-l freelist regarding ”cursor: pin S wait on X” event . Tanel Poder replied to this thread with excellent explanation. You can read full thread here

This wait happens when your session wants to examine / execute an existing child cursor – but it’s being pinned in X mode by someone else. The usual reasons for this are: 1) The cursor got flushed out for some reason (or it’s just the first time anyone is running it) and the other session is currently hard parsing/compiling/loading the contents of this cursor back into library cache. The longer the parsing/loading takes, the longer you wait. If the parsing session hits some (performance) issues, this pin may be held for a long time.

2) The child cursor is already loaded into cache, but someone else is currently flushing it out (shared pool shortage, fragmentation & loading of [Read More]

Moving WordPress Blog to Amazon Web Services

Why I moved?Setting up AWS EC2 InstanceConfiguring EC2 InstanceMigrating WordPress DataOptimizing Webhost SetupMaking actual MoveOptimizing WordPress with w3tcIs Free AWS actually Free?How much will it cost?SummaryReferences

Why I moved?

Last Month I had moved my blog hosting from Dreamhost to Amazon Web Services(AWS). I decided to change hosting provider as my blog suffered 17 outages (as per pingdom report for month of December 2012)

Uptime Outages Response time 97.33% 17 2359 ms

Dreamhost mentioned that my wordpress processes were running out of memory (30M) and it was being killed by their memory manager. They recommended to either uninstall some of plugins or move to Virtual Private server at additional cost of $15 per month (300M memory) At this point I decided to explore other hosting providers. I had read about Amazon web service and searching on internet revealed articles detailing setup (mentioned in reference section below). I decided for AWS as it provides free [Read More]

11.2 AQ – Messages stuck in PROCESESED,WAIT State

I had been working today on AQ issue where messages were not moving from WAIT to READY State.This was on 11.2.0.3 database running on RHEL5u6(64 bit). I thought of quickly documenting it for easy reference When you enqueue record, you can specify delay after which record should be ready for dequeue. During this time, MSG_STATE is in ‘WAIT’ state, which changes to ‘READY’ state after current time reaches delay timestamp.All the stuck records were having delay_timestamp past current time.This can be verified by querying AQ$Queue_table.

Below example , you see that MSG_ID ‘D2DA56B9746E2E59E043096F93452D8A‘ has delay_timestamp of ’10-JAN-13 05.42.07.835125 AM’ which is less then current time ( 9:52 AM)

09:52:00 SQL > select msg_id,msg_state,enq_timestamp,delay_timestamp from AQ$TEST_QTABLE where rownum <5; MSG_ID MSG_STATE ENQ_TIMESTAMP DELAY_TIMESTAMP ——————————– ———- —————————— —————————— D2DA56B94EF02E59E043096F93452D8A WAIT 09-JAN-13 07.37.07.595334 AM 23-FEB-13 07.37.07.595334 AM D2DA56B9746E2E59E043096F93452D8A WAIT 09-JAN-13 07.42.07.835125 AM 10-JAN-13 05.42.07.835125 AM D2DA56B94ED32E59E043096F93452D8A WAIT 09-JAN-13 07.37.05.274447 AM 23-FEB-13 07.37.05.274447 AM D2F1EDF5112D59A1E043096F93451A42 READY 10-JAN-13 07.35.33.089265 AM [Read More]

Using SQL commands on ADRCI

While working with ADRCI , I came to know that we can run normal SQL commands for some of the operation.e.g To display current purge policy , you can run select * from adr_control instead of show control.

adrci> desc ADR_CONTROL Name Type NULL? —————————– ————— ———– ADRID number SHORTP_POLICY number LONGP_POLICY number LAST_MOD_TIME timestamp LAST_AUTOPRG_TIME timestamp LAST_MANUPRG_TIME timestamp ADRDIR_VERSION number ADRSCHM_VERSION number ADRSCHMV_SUMMARY number ADRALERT_VERSION number CREATE_TIME timestamp

Since I am only interested in few columns, I can create a view

adrci> create view my_control as select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL; View MY_CONTROL Created

Now you can query this new view

adrci> select * from my_control; ADR Home = /home/oracle/diag/rdbms/tintin/tintin1: ************************************************************************* SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ——————– ——————– —————————————- —————————————- 720 8760 2012-12-27 23:09:47.140711 +00:00 1 rows fetched

You can find the list of all metadata tables under ADR_HOME/metadata.

oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/metadata>ls -ltr HM* -rw-r—– 1 oracle dba 65536 Jan 13 2012 HM_INFO.ams -rw-r—– 1 oracle dba 65536 [Read More]

Happy New Year – Goodbye 2012 and welcome 2013

It seems Mayan’s freaked out lot of people by not making 2013 calendar Anyways our world survives and we will be entering 2013 in another 7 hours. So wishing all Oracle Community members a “Happy And Prosperous New Year”

This year we have heard lot of buzz around “Big Data” and it seems to be gaining popularity in Data warehousing World. Oracle will be releasing its Oracle 12c database next year with emphasis on Cloud features. Let’s wait and see what is in store for us.

This year’s annual wordpress stats report is out and we clocked 230,000 pageviews in 2012. We have not been active this year and have got most of the hits on older articles. We hope to improve it in coming year.

On personal front this was Wonderful year. My younger sister got married this year,Saurabh was blessed with Baby Boy and Abhishek had a Sweet little Angel.

Btw I have [Read More]

Disclaimer!

Ask Question

Archives

Recovering from Lost AWS EC2 Key

cursor: pin S wait on X

Moving WordPress Blog to Amazon Web Services

11.2 AQ – Messages stuck in PROCESESED,WAIT State

Using SQL commands on ADRCI

Happy New Year – Goodbye 2012 and welcome 2013

Do you know?

Subscribe to Blog via Email

What’s Popular

Recent Comments