Quick note for people using NFS for shared storage on RAC database. Till RHEL5 we had to ensure nfs,nfslock and portmap service has to be running. These services are required otherwise you will get following errors while mounting database
ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/oradata/orcl/control01.ctl' ORA-27086: unable to lock file – already in use
Mostly this could be auto-enabled on boot by using chkconfig command. While working on similar issue today, I found out that this service is not present in RHEL 6
# service portmap status portmap: unrecognized service
The portmap service was used to map RPC program numbers to IP address port number combinations in earlier versions of Red Hat Enterprise Linux. As per RHEL6 docs, portmap service has been replaced by rpcbind in Red Hat Enterprise Linux 6 to enable IPv6 support. So following command will work
# service rpcbind status rpcbind (pid 1587) is running…
You [Read More]
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]
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]
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]
I had been working today on AQ issue where messages were not moving from WAIT to READY State.This was on 22.214.171.124 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]
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]