Unfortunately, on the 6th September thief broke the door of my rented flat and took away my laptop, money, modem and gold ornaments. As a result I bought new Grameenphone modem, Grameenphone Internet SIM from Uttoron Computer, Shailakupa, Jhenidah on 8th September. After I bought the postpaid Internet SIM they informed me that within 72 hours my SIM will be activated. In the meantime they also told me they will send my information tomorrow morning and so I expect my SIM activated by tomorrow. Well, tomorrow arrived and it was afternoon. I keep trying to connect to Internet but SIM registration failed. I thought they said 72 hours, so let me see after 72 hours. After 72 hours I again tried to connect to Internet, still SIM registration failed. Then I contacted them through mobile phone. They said it should not be. They told me to go to their center with modem and SIM. I went to them and saw their center is closed.
After it is 6 days passed and EID is finished I again contacted them through mobile phone. This time to the owner of the shop. He feels hesitate and ringed to the customer manager indicating that the paper of badly filled and my SIM will not be activated. He felt sorry to me and told me to wait till tomorrow. In the meantime, I have to come back in Dhaka but still did not see any good response. I contacted to grammeenphone online help through calling 121 but they informed me the SIM is not registered yet and until it is registered they have nothing to do. Rather the customer manager told me to contact the shop from where I bought the SIM. Anyhow, this time I contacted to my agent and they told me by tomorrow my SIM will be registered. Hopefully the next day at night I am succeed to connect to Internet. So it took my 9 took to activate my SIM package!
Well, I have recharged 1000 taka and activated P2 unlimited package from Grameenphone. Note that, I always read that package P2 is unlimited that means you can download and upload as much as you can. Though Grameenphone Internet speed is not good but still they have introduced fair usage policy which is funny really. Anyway according to P2 unlimited package fair usage policy any subscriber whose monthly usage is over 5G they will be implied to fair usage policy. BOGUS AND BULLSHIT GRAMEENPHONE. But there is more pathetic news coming.
According to them,
Grameenphone's Unlimited Plans are designed for subscribers who do not want to receive a bill beyond a certain amount and for those who wish to enjoy unlimited access to their internet. However, in the event that usage exceeds the regular acceptable levels in accordance with the Fair Usage Policy, subscribers shall not be charged for their excess usage. Instead, a restriction shall be placed on their data transfer rates beyond their regular usage in order to ensure the quality of service for all regular customers.
Unlimited Plan subscribers who regularly exceed the accepted level of usage will be affected by this Fair Usage Policy. Under the terms of GP's unlimited internet plan, no charge will be applied for excess usage, however upon exceeding the stipulated volume limit, the subscriber’s speed shall be restricted for the remaining period within their current bill cycle or validity period or that particular day depending on the package they are using. Normal service speed shall be restored from the first day of the next bill cycle or subscription renewal.
Now I have activated P2 package at 17th September. As I bought a new laptop so for it I downloaded different software like xampp, filezilla, antivirus and tried to download windows operating system. But in the middle I see everything stuck. It was happened at 3rd October. After I have connecting to GP internet, I see I am not able to connect any chat system like yahoo messenger, gtalk or skype. Even I can't browse any website. After 10 hours of trying at last I have logged in to online GP customer care and no one of them able to give me right answer. Well, the pathetic scenario is after I connect to Internet, GP automatically disconnects me after 1/2/3 minutes. It sounds in a hour I get disconnect 30 times. IMAGINE!! Once again I call them to 121 and at last I have heard their ridiculous fair usage policy. According to them after you exceed 5G limit your Internet connection will be limited to 3KBS and hardly I get around 1KBPS after connecting to their system, though my connection spans at best 3 minutes.
Ok, I agree to them that I have exceed 5G limit and hence I have imposed a limit, but whenever I ask them why I am getting frequent disconnection they did not able to tell me any good answer. As GP charges daily basis on P2 package so I decided to cancel the P2 package and wish to activate P5 package. They told me to type "cancel" and send it to 5000. So I did it but I did not get any confirmation of cancellation. So I again asked them and they told me, to activate P5 package you need to recharge 450 taka more. As I have already around 400 taka in my account, so recharge 450 more and it will be 850 taka and P5 package including vat is 805 taka. As I know I am postpaid user and I have 50 taka credit limit but still I recharged said amount.
Ok, after I recharged said amount Saikat (who is one of the grameenphone online customer helper) informed me that my account has been credited and he has activated my P5 package. So now I will be able to use my Internet without any problem. After I came back from my shop whenever I try to connect to my Internet, after it connects GP automatically disconnects my connection. Now more problem comes, even I could not connect to Internet. After so much trying I saw connection exists only for few seconds. So, I contacted them and they told me as I explicitly typed "cancel" so my P2 package status becomes pending. Now they will not be able to do anything. I need to wait till 12 am in order to reset P2 and activate P5.
Well, I waited till 12 am and whenever I tried to connect the same news. It automatically disconnects. At morning I explicitly sent message to 5000 for P5 package and it said to me my P5 package has been successfully activated. Now I tried to connect but still I get horrible speed and my connection spans for 2 minutes only. After 2 minutes, it does not allow me to connect anymore. I contacted to their support after calling 121. They told me I don't have sufficient balance and I need to recharge 50 taka more to use Internet. IMAGINE!!! I ALREADY ACTIVATED P5 PACKAGE AND NOW THEY TOLD ME 50 TAKA MORE. Anyway, I went to shop and recharged 50 taka. Now I came back but same scenario I get disconnects immediately after connection established. Again I call them 121 , they told me the problem might be in my modem, so they suggest me to go to Mirpur-1 customer care. (The real thing is due to GP system Technical bug whenever I connected using P5 package it did not connect to P5 rather it connects to P1 ridiculous package and so within minutes it charged me 30 taka and I dont have any modem problem.)
Anyway, as they have suggested me I went to Mirpur-1 Grameenphone customer care. The customer manager inserted my modem to his laptop and he is getting disconnection just after connection established. He told me to go to Grameenphone service center. Note that in Dhaka there is 2 Grameenphone service center exists.
1. Agent Name: Discovery
Address: Suite 13/A, Chandrshilla Suvastu Tower, 69/1 Panthapath, Dhaka-1205.
There Samasung service center is Grameenphone modem service center.
2. Agent Name: Mobile Care
Address: 5th Floor, price plaza, 4/2 Sobhanbag, Mirpur Road, Dhanmondi, Dhaka-1207
The customer manager told me to go to Sobhanbag service center. I went to Sobhanbag but unfortunately at Tuesday price plaza is closed and so customer service center is closed too. So, I went to pantha path and in 13th floor. I saw several customers came with GP modem problem and unfortunately it seemed to me all are same problem like me and GP provided them false information about modem problem. Anyway, when my serial came, I told my problem then the customer service manager told me it is common problem for Grameenphone Internet modem model E1550. So she flushed my firmware and put a new updated version software. I did not bring my laptop. When I came back home I put the modem into my laptop. Now problem has changed and in fact it brought more problem. Now whenever I insert my modem into laptop it even can't recognize my SIM!!!
I am totally frustrated with GP as well as with their customer service at this point. At last I remember Kaosar vai about my frustration who is a member of GP employee and work in the customer service department. He informed me that it is totally GP problem and he will take care of it tomorrow because he was ill. Ok, I wait for tomorrow but till afternoon I see no good news. So I again go to grameenphone customer care and now instead of trying in their laptop they told me to go to there with my laptop! What would happen if the problem was in my desktop computer. I would go to there with my desktop pc!
Anyway, while I am in grammenphone center Kaosar vai informed me that my problem resolved and he wants me to try. I came back home and it worked really but not using this new modem driver. Alhamdulillah, I had old driver in my another laptop.
Friday, October 8, 2010
Sunday, October 3, 2010
Step by step Create an Oracle 11gR2 Physical Standby Database
In this article I will show step by step procedure to create an Oracle 11gR2 physical standby database. This example environment is as follows:
- Both primary and standby database are on RAC environment. Both are 2 nodes RAC.
- The distance between primary and standby database is 300 kilometers and they are within Bangladesh.
- There is 10 Mbps dedicated bandwidth between primary and standby database.
- All 4 nodes are using Red Hat Linux Enterprise version 5 operating system and same hardware structure.
- Oracle 11gR2 is installed both in primary and physical standby database.
- Oracle datafiles and clusterware are on ASM file system.
Though this example is based on RAC and ASM, you can use this document to setup any type of physical standby database. I will tell explicitly what to do based on whether your environment is non-RAC or not, whether you use ASM or not.
In this example the following values are used:
- Database name is bddipdc
- Primary database unique name as well as oracle net service name is bddipdc
- Standby database unique name as well as oracle net service name is bddipdrs
- Primary database hostname is DC-DB-01 and standby database hostname is DRS-DB-01
- Primary database IP Address is 192.168.100.101 and standby database IP Address is 192.168.105.101
Step 01: Prepare the Primary and physical standby database environment: (Both bddipdc and bddipdrs)
i) After you have physically placed hardware in both site install operating system in both end. The site in which primary database reside called as Data Center (DC) and the site in which standby database reside called as Disaster Recovery Site (DRS) and that is I choose the unique name as bddipdc and bddipdrs correspondingly.
ii) Install Oracle software as well as oracle database both in primary and standby database site. Though database creation in standby site is not mandatory but you can create database specially if you are in RAC environment. Because in that case you don't need to do many manual tasks like register database to cluster registry etc. Note that both in primary and standby site, while creating database keep the database name same that is bddipdc.
Step 02: Prepare the Primary Database for Standby Database Creation: (Only bddipdc)
i) Enable force logging on primary database.
SQL> ALTER DATABASE FORCE LOGGING;
ii) Set the database initialization parameters.
iii) Enable archivelog in primary database.
If the primary database is not in archivelog mode then enable archivelog in primary database. If your database is in archivelog mode then you can simply forward to next phase.
Step 03: Determine datafiles, controlfiles, password file, initialization parameter files to be copied from primary to standby database (bddipdc):
You can create standby database using RMAN. But in this post I want to show all steps manually because RMAN has several bug while creating another copy of database over the network.
i)Connect to one Oracle instance and determine the location of datafiles and tempfiles. Save the file location in a notepad.
Determine the location of datafile,
SQL> select name from v$tempfile;
NAME
----------------------------------
+DATA/bddipdc/tempfile/temp.271.728507701
ii)Create the pfile from spfile of primary database.
SQL> conn / as sysdba
SQL> create pfile='/tmp/bddipdc/initbddipdc.ora' from spfile;
iii) Shutdown the primary database. If you are in RAC environment, then shut down all instances.
SQL> shut immediate;
Using srvctl,
$ srvctl stop database -d bddipdc
iv) This step is only applicable if your database files are in ASM. As our datafiles and tempfiles are in ASM file system so we need to copy them under /tmp/bddipdc in order to move the datafiles and tempfiles into another computer.
Ensure that your ORACLE_SID and ORACLE_HOME points to ASM home and ASM SID.
Invoke asmcmd.
SQL> startup mount;
Create the standby control file.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/bddipdc/control01.ora';
Step 04: Copy password file, datafile, standby controlfile, pfile from primary database to standby database.
Up to now we have datafile, tempfile, standby controlfile, pfile under one directory /tmp/bddipdc in the primary database. Now also we need to move password file into /tmp/bddipdc directory so that we can simply scp to remote location and with one command all files will be copied to standby database.
$ scp -r /tmp/bddipdc oracle@drs-db-01:/tmp/bddipdc
Copy node 1 of primary database password file across all nodes in standby database (This one is only applicable for RAC)
The password file of node 1 of primary database need to be copied across all instances in standby database as well as all other nodes in primary database as well.
Step 05: Start the Standby database and set initialization parameters. (bddipdrs)
At this stage we have all required files under location /tmp/bddipdc to setup physical standby database. We have already one database created in standby site. So we can use that spfile for our standby database creation. Though we have pfile in the location /tmp/bddipdc (which we copied from primary database) we will use current spfile of existing standby database in order minimize the steps.
i) Shut down standby database (in RAC shut down all instances) and start in nomount state (in RAC start one instance in nomount stage).
SQL> shut immediate;
SQL> startup nomount;
Set the initialization parameters.
alter system set control_files='+DATA/bddipdc/controlfile/control01.ctl' scope=spfile sid='*';
iii) shutdown the standby database.
SQL> shut immediate;
iv) Delete all control files, datafiles, tempfiles of current standby database and copy these standby control file, datafile, tempfile into ASM file system. I am keeping the path of these files same as primary database. Note that if you are not in ASM file system then instead of +DATA use your desired location.
SQL> startup mount
Set the undo_management parameter manual.
SQL> alter system set undo_management=MANUAL scope=both sid='*';
Now rename datafiles and tempfiles in order to use the correct location as we copied to ASM file system.
In all nodes configure TNS entry so that you can reach from primary to standby database and vice versa.
Within the file $ORACLE_HOME/network/admin/tnsnames.ora add the following two entries.
$ lsnrctl status
You should put an static listener entry within $GRID_HOME/network/admin/listener.ora if you are in RAC
and you should put an static listener entry within $ORACLE_HOME/network/admin/listener.ora if you are in non RAC system.
Following is an static entry for node 1 from primary database.
Following is an static listener entry for node 2 in primary database.
Following is the listener entry for the node 1 in standby database.
Step 7: Verify that TNS names and password file across all the instances are working properly. (All nodes):
Both in primary and standby database across all nodes issue,
Step 8: Create standby redo log and start redo apply on standby database (bddipdrs):
Create standby redo logfile on standby database bddipdrs by following command,
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Step 09: Verify that standby database is performing properly.
i)Identify the existing archived redo log files. (bddipdrs -standby)
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
ii)Force a log switch to archive the current online redo log file.
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:
SQL> ALTER SYSTEM SWITCH LOGFILE;
iii) Verify the new redo data was archived on the standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
In the output you should see one extra row than previous query.
iv)Verify that received redo has been applied.
On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
- Both primary and standby database are on RAC environment. Both are 2 nodes RAC.
- The distance between primary and standby database is 300 kilometers and they are within Bangladesh.
- There is 10 Mbps dedicated bandwidth between primary and standby database.
- All 4 nodes are using Red Hat Linux Enterprise version 5 operating system and same hardware structure.
- Oracle 11gR2 is installed both in primary and physical standby database.
- Oracle datafiles and clusterware are on ASM file system.
Though this example is based on RAC and ASM, you can use this document to setup any type of physical standby database. I will tell explicitly what to do based on whether your environment is non-RAC or not, whether you use ASM or not.
In this example the following values are used:
- Database name is bddipdc
- Primary database unique name as well as oracle net service name is bddipdc
- Standby database unique name as well as oracle net service name is bddipdrs
- Primary database hostname is DC-DB-01 and standby database hostname is DRS-DB-01
- Primary database IP Address is 192.168.100.101 and standby database IP Address is 192.168.105.101
Step 01: Prepare the Primary and physical standby database environment: (Both bddipdc and bddipdrs)
i) After you have physically placed hardware in both site install operating system in both end. The site in which primary database reside called as Data Center (DC) and the site in which standby database reside called as Disaster Recovery Site (DRS) and that is I choose the unique name as bddipdc and bddipdrs correspondingly.
ii) Install Oracle software as well as oracle database both in primary and standby database site. Though database creation in standby site is not mandatory but you can create database specially if you are in RAC environment. Because in that case you don't need to do many manual tasks like register database to cluster registry etc. Note that both in primary and standby site, while creating database keep the database name same that is bddipdc.
Step 02: Prepare the Primary Database for Standby Database Creation: (Only bddipdc)
i) Enable force logging on primary database.
SQL> ALTER DATABASE FORCE LOGGING;
ii) Set the database initialization parameters.
#Primary database role initialization parameters.As primary database is using spfile so run the following commands on primary database.
DB_NAME=bddipdc
DB_UNIQUE_NAME=bddipdc
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc'
LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
# Standby role initialization parameters.
FAL_SERVER=bddipdrs
DB_FILE_NAME_CONVERT='bddipdrs','bddipdc'
LOG_FILE_NAME_CONVERT='bddipdrs','bddipdc'
STANDBY_FILE_MANAGEMENT=AUTO
alter system set DB_UNIQUE_NAME=bddipdc scope=spfile sid='*';We have not set any DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters because in standby database we want to keep same file structure as it is in primary database.
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bddipdrs scope=both sid='*';
alter system set FAL_CLIENT=bddipdc scope=both sid='*';
iii) Enable archivelog in primary database.
If the primary database is not in archivelog mode then enable archivelog in primary database. If your database is in archivelog mode then you can simply forward to next phase.
SQL> SHUTDOWN IMMEDIATE;If your database is in RAC environment then you can follow Enable archivelog for RAC. If you are in non RAC environment then you can follow Change archivelog mode .
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
Step 03: Determine datafiles, controlfiles, password file, initialization parameter files to be copied from primary to standby database (bddipdc):
You can create standby database using RMAN. But in this post I want to show all steps manually because RMAN has several bug while creating another copy of database over the network.
i)Connect to one Oracle instance and determine the location of datafiles and tempfiles. Save the file location in a notepad.
Determine the location of datafile,
SQL> select name from v$datafile;Determine the location of oracle tempfile.
NAME
----------------------------------------------------
+DATA/bddipdc/datafile/system.267.728507607
+DATA/bddipdc/datafile/sysaux.259.728507607
+DATA/bddipdc/datafile/undotbs1.258.728507609
+DATA/bddipdc/datafile/users.257.728507609
+DATA/bddipdc/datafile/undotbs2.270.728507783
SQL> select name from v$tempfile;
NAME
----------------------------------
+DATA/bddipdc/tempfile/temp.271.728507701
ii)Create the pfile from spfile of primary database.
SQL> conn / as sysdba
SQL> create pfile='/tmp/bddipdc/initbddipdc.ora' from spfile;
iii) Shutdown the primary database. If you are in RAC environment, then shut down all instances.
SQL> shut immediate;
Using srvctl,
$ srvctl stop database -d bddipdc
iv) This step is only applicable if your database files are in ASM. As our datafiles and tempfiles are in ASM file system so we need to copy them under /tmp/bddipdc in order to move the datafiles and tempfiles into another computer.
Ensure that your ORACLE_SID and ORACLE_HOME points to ASM home and ASM SID.
Invoke asmcmd.
$asmcmdv) Start one instance of primary database in mount state. If you are in non RAC environment then just start database in mount state.
ASMCMD> cp +DATA/bddipdc/datafile/system.267.728507607 /tmp/bddipdc/system
ASMCMD> cp +DATA/bddipdc/datafile/sysaux.259.728507607 /tmp/bddipdc/sysaux
ASMCMD> cp +DATA/bddipdc/datafile/undotbs1.258.728507609 /tmp/bddipdc/undotbs1
ASMCMD> cp +DATA/bddipdc/datafile/users.257.728507609 /tmp/bddipdc/users
ASMCMD> cp +DATA/bddipdc/datafile/undotbs2.270.728507783 /tmp/bddipdc/undotbs2
ASMCMD> cp +DATA/bddipdc/tempfile/temp.271.728507701 /tmp/bddipdc/temp
SQL> startup mount;
Create the standby control file.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/bddipdc/control01.ora';
Step 04: Copy password file, datafile, standby controlfile, pfile from primary database to standby database.
Up to now we have datafile, tempfile, standby controlfile, pfile under one directory /tmp/bddipdc in the primary database. Now also we need to move password file into /tmp/bddipdc directory so that we can simply scp to remote location and with one command all files will be copied to standby database.
$ scp -r /tmp/bddipdc oracle@drs-db-01:/tmp/bddipdc
Copy node 1 of primary database password file across all nodes in standby database (This one is only applicable for RAC)
The password file of node 1 of primary database need to be copied across all instances in standby database as well as all other nodes in primary database as well.
Step 05: Start the Standby database and set initialization parameters. (bddipdrs)
At this stage we have all required files under location /tmp/bddipdc to setup physical standby database. We have already one database created in standby site. So we can use that spfile for our standby database creation. Though we have pfile in the location /tmp/bddipdc (which we copied from primary database) we will use current spfile of existing standby database in order minimize the steps.
i) Shut down standby database (in RAC shut down all instances) and start in nomount state (in RAC start one instance in nomount stage).
SQL> shut immediate;
SQL> startup nomount;
Set the initialization parameters.
SQL> alter system set DB_UNIQUE_NAME=bdafisdc scope=spfile sid='*';We also set the location of control_files. We will copy standby control file into this location.
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bdafisdc,bdafisdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bdafisdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bdafisdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bdafisdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bdafisdrs scope=both sid='*';
alter system set FAL_CLIENT=bdafisdc scope=both sid='*';
alter system set control_files='+DATA/bddipdc/controlfile/control01.ctl' scope=spfile sid='*';
iii) shutdown the standby database.
SQL> shut immediate;
iv) Delete all control files, datafiles, tempfiles of current standby database and copy these standby control file, datafile, tempfile into ASM file system. I am keeping the path of these files same as primary database. Note that if you are not in ASM file system then instead of +DATA use your desired location.
$ asmcmdv) Start the standby database in mount state.
ASMCMD> cp /tmp/bddipdc/control01.ora +DATA/bddipdc/controlfile/control01.ctl
ASMCMD> cp /tmp/bddipdc/system +DATA/bddipdc/datafile/system
ASMCMD> cp /tmp/bddipdc/sysaux +DATA/bddipdc/datafile/sysaux
ASMCMD> cp /tmp/bddipdc/undotbs1 +DATA/bddipdc/datafile/undotbs1
ASMCMD> cp /tmp/bddipdc/undotbs2 +DATA/bddipdc/datafile/undotbs2
ASMCMD> cp /tmp/bddipdc/users +DATA/bddipdc/datafile/users
ASMCMD> cp /tmp/bddipdc/temp /tmp/bddipdc/temp
SQL> startup mount
Set the undo_management parameter manual.
SQL> alter system set undo_management=MANUAL scope=both sid='*';
Now rename datafiles and tempfiles in order to use the correct location as we copied to ASM file system.
SQL> alter database rename file '+DATA/bddipdc/datafile/system.267.728507607' to '+DATA/bddipdc/datafile/system';Step 06: Configure TNS entry and listener across all nodes. (All nodes)
SQL> alter database rename file '+DATA/bddipdc/datafile/sysaux.259.728507607' to '+DATA/bddipdc/datafile/sysaux';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs1.258.728507609' to '+DATA/bddipdc/datafile/undotbs1';
SQL> alter database rename file '+DATA/bddipdc/datafile/users.257.728507609' to '+DATA/bddipdc/datafile/users';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs2.270.728507783' to '+DATA/bddipdc/datafile/undotbs2';
SQL> alter database rename file '+DATA/bddipdc/tempfile/temp.271.728507701' to '+DATA/bddipdc/datafile/temp';
In all nodes configure TNS entry so that you can reach from primary to standby database and vice versa.
Within the file $ORACLE_HOME/network/admin/tnsnames.ora add the following two entries.
BDDIPDRS =Before configuring TNS entry be sure about your host name, port number and service name. You can easily check that by issuing,
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DRS-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)
BDDIPDC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DC-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)
$ lsnrctl status
You should put an static listener entry within $GRID_HOME/network/admin/listener.ora if you are in RAC
and you should put an static listener entry within $ORACLE_HOME/network/admin/listener.ora if you are in non RAC system.
Following is an static entry for node 1 from primary database.
SID_LIST_LISTENER =Note: If you are in non RAC system then instead of SID bddipdc1 you must use Database name that is bddipdc.
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-01)(PORT = 1521))
)
)
Following is an static listener entry for node 2 in primary database.
SID_LIST_LISTENER =Note: Just host name and Oracle SID name is change for node 2.
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-02)(PORT = 1521))
)
)
Following is the listener entry for the node 1 in standby database.
SID_LIST_LISTENER =Note: Just I have changed the hostname. If you are non RAC system then instead of instance SID bddipdc1 use database name bddipddc instead.
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DRS-DB-01)(PORT = 1521))
)
)
Step 7: Verify that TNS names and password file across all the instances are working properly. (All nodes):
Both in primary and standby database across all nodes issue,
$ tnsping bddipdcIf you can connect to both primary and standby instances using TNS entry from all nodes through sqlplus then your password file as well as TNS entry is perfect.
$ tnsping bddipdrs
$ sqlplus sys/password@bddipdc as sysdba
$ sqlplus sys/password@bddipdrs as sysdba
Step 8: Create standby redo log and start redo apply on standby database (bddipdrs):
Create standby redo logfile on standby database bddipdrs by following command,
alter database add standby logfile thread 1 size 50M;Or, simply you can run following command if you are in non RAC environment.
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile size 50M;Set the undo_management parameter to AUTO.
SQL> alter system set undo_management=AUTO scope=both sid='*';Start Redo Apply,
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Step 09: Verify that standby database is performing properly.
i)Identify the existing archived redo log files. (bddipdrs -standby)
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
ii)Force a log switch to archive the current online redo log file.
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:
SQL> ALTER SYSTEM SWITCH LOGFILE;
iii) Verify the new redo data was archived on the standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
In the output you should see one extra row than previous query.
iv)Verify that received redo has been applied.
On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Wednesday, September 29, 2010
How to install Oracle 10g on windows vista or windows 7
Step 01: Download the appropriate Oracle software version.
The first step is to download oracle software for windows vista or windows 7. So determine your hardware and operating system in order to determine whether you will download oracle 32 bit or 64 bit. You can follow the post http://arjudba.blogspot.com/2008/05/how-to-identify-os-or-oracle-64-bit-or.html in order to determine which version of oracle you will download.
You can get the oracle software from two ways.
Either download from
OTN (technet.oracle.com)
or from
edelivery (edelivery.oracle.com)
I am showing the downloads way from OTN (technet.oracle.com).
You can directly go to page http://www.oracle.com/technetwork/database/database10g/downloads/index.html in order to download Oracle 10g.
If you want to download 64 bit oracle for your windows vista or windows 7 then go to http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html.
If you want to download 64 bit oracle for your windows vista or windows 7 then go to http://www.oracle.com/technetwork/database/10203vista-087538.html to download.
In summary,
For 32bit Windows: Oracle Database 10g Release 2 (10.2.0.3/10.2.0.4) for Microsoft Windows Vista and Windows 2008
For 64bit Windows: Oracle Database 10g Release 2 (10.2.0.4) for Microsoft Windows Vista x64 and Microsoft Windows Server 2008 x64
With 32 bit downloads you can install on Windows Vista, Windows Server 2008 and Windows 7.
With 64 but downloads you can install on Windows Vista x64, Windows Server 2008 x64, Windows 7 x64 and Windows Server 2008 R2 x64.
Step 02: Run the installer (OUI).
Double click on setup.exe. Ignore the following prerequisite errors displayed by Oracle Universal Installer and complete the installation:
i) Checking operating system requirements
ii) Checking service pack requirements
(Click on the Ignore Errors Check Box)
Another option is to run the following command from the command line:
change to the directory where the Oracle files were extracted.
Then run the command:
> setup.exe -ignoreSysprereqs
(command is case sensitive)
This will ignore the Pre-Requisite checks.
The first step is to download oracle software for windows vista or windows 7. So determine your hardware and operating system in order to determine whether you will download oracle 32 bit or 64 bit. You can follow the post http://arjudba.blogspot.com/2008/05/how-to-identify-os-or-oracle-64-bit-or.html in order to determine which version of oracle you will download.
You can get the oracle software from two ways.
Either download from
OTN (technet.oracle.com)
or from
edelivery (edelivery.oracle.com)
I am showing the downloads way from OTN (technet.oracle.com).
You can directly go to page http://www.oracle.com/technetwork/database/database10g/downloads/index.html in order to download Oracle 10g.
If you want to download 64 bit oracle for your windows vista or windows 7 then go to http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html.
If you want to download 64 bit oracle for your windows vista or windows 7 then go to http://www.oracle.com/technetwork/database/10203vista-087538.html to download.
In summary,
For 32bit Windows: Oracle Database 10g Release 2 (10.2.0.3/10.2.0.4) for Microsoft Windows Vista and Windows 2008
For 64bit Windows: Oracle Database 10g Release 2 (10.2.0.4) for Microsoft Windows Vista x64 and Microsoft Windows Server 2008 x64
With 32 bit downloads you can install on Windows Vista, Windows Server 2008 and Windows 7.
With 64 but downloads you can install on Windows Vista x64, Windows Server 2008 x64, Windows 7 x64 and Windows Server 2008 R2 x64.
Step 02: Run the installer (OUI).
Double click on setup.exe. Ignore the following prerequisite errors displayed by Oracle Universal Installer and complete the installation:
i) Checking operating system requirements
ii) Checking service pack requirements
(Click on the Ignore Errors Check Box)
Another option is to run the following command from the command line:
change to the directory where the Oracle files were extracted.
Then run the command:
> setup.exe -ignoreSysprereqs
(command is case sensitive)
This will ignore the Pre-Requisite checks.
Renaming global database name hang with row cache enqueue lock
Problem Description
Renaming global database name hangs and never completes. That is following statements hangs,
SQL> alter database rename global_name to orcl.world;
From the alert log we see the a tracefile is generated containing following entry.
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< row cache enqueue: session: 0x11d4fd018, mode: N, request: X
Cause of the Problem
The database was using Database links, which in fact using database GLOBAL_NAME. The ALTER DATABASE RENAME requires an exclusive lock which was waiting for the database link session to end and release their lock on the underlying data dictionary table.
Solution of the Problem
Ensure that currently no active database link session exist and then you can issue rename global_name command.
Alternatively, you can do the following:
SQL> shutdown immediate
SQL> startup restrict
SQL> alter database rename global_name to new name;
SQL> alter system disable restricted session;
Renaming global database name hangs and never completes. That is following statements hangs,
SQL> alter database rename global_name to orcl.world;
From the alert log we see the a tracefile is generated containing following entry.
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<< row cache enqueue: session: 0x11d4fd018, mode: N, request: X
Cause of the Problem
The database was using Database links, which in fact using database GLOBAL_NAME. The ALTER DATABASE RENAME requires an exclusive lock which was waiting for the database link session to end and release their lock on the underlying data dictionary table.
Solution of the Problem
Ensure that currently no active database link session exist and then you can issue rename global_name command.
Alternatively, you can do the following:
SQL> shutdown immediate
SQL> startup restrict
SQL> alter database rename global_name to new name;
SQL> alter system disable restricted session;
Primary DB freezed with waited too long for a row cache enqueue lock
Problem Description
On Oracle database 10.2.0.3.0 Data Guard with Broker configuration whenever there is an attempting to restart the standby in read only, the following error occurs:
ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12170: TNS:Connect timeout occurred
PING[ARC6]: Error 3113 when pinging standby
ARC6: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1089)
The errors occurred in standby database and primary database hangs!
Cause of the Problem
The above error is caused by LGWR process in Oracle Data Guard having RAC environment and it is in fact Oracle bug. The bug number is 7487408.
The bug fires whenever Data Guard is managed by Broker and standby database is shutdown or opened in read only mode. The Primary still tries to ship/send redo streams to standby in SYNC mode and eventually hangs.
Solution of the Problem
- Download the optach 7487408 from oracle support/metalink in order to solve the bug.
- The above bug is fixed in Oracle patchset 10.2.0.5. So you need to upgrade your oracle database to 10.2.0.5.
On Oracle database 10.2.0.3.0 Data Guard with Broker configuration whenever there is an attempting to restart the standby in read only, the following error occurs:
ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12170: TNS:Connect timeout occurred
PING[ARC6]: Error 3113 when pinging standby
ARC6: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1089)
The errors occurred in standby database and primary database hangs!
Cause of the Problem
The above error is caused by LGWR process in Oracle Data Guard having RAC environment and it is in fact Oracle bug. The bug number is 7487408.
The bug fires whenever Data Guard is managed by Broker and standby database is shutdown or opened in read only mode. The Primary still tries to ship/send redo streams to standby in SYNC mode and eventually hangs.
Solution of the Problem
- Download the optach 7487408 from oracle support/metalink in order to solve the bug.
- The above bug is fixed in Oracle patchset 10.2.0.5. So you need to upgrade your oracle database to 10.2.0.5.
Tuesday, September 28, 2010
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
This article will explain some reasons for which you may encounter "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" message in the alert log file. When Row cache contention occurs, if the enqueue cannot be obtained within a certain time period, a trace file will be generated in the trace location with some trace details.
The trace file tends to contain the following words:
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<
/opt/oracle/admin/EAIAPP/udump/eaiapp1_ora_23288.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/dbs
System name: Linux
Node name: db1-eai.prod.stl.cw.intraisp.com
Release: 2.6.18-128.4.1.el5
Version: #1 SMP Thu Sep 23 19:59:19 EDT 2010
Machine: x86_64
Instance name: EAIAPP1
Redo thread mounted by this instance: 1
Oracle process number: 43
Unix process pid: 23288, image: oracle@db1-eai.prod.stl.cw.intraisp.com
*** 2010-09-25 17:08:05.532
*** ACTION NAME:() 2010-09-25 17:08:05.532
*** MODULE NAME:(OMS) 2010-09-25 17:08:05.532
*** SERVICE NAME:(EAIAPP) 2010-09-25 17:08:05.532
*** SESSION ID:(120.29614) 2010-09-25 17:08:05.532
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000001b542d78, mode: N, request: S
row cache parent object: address=700000036f27328 cid=0(dc_tablespaces)
hash=a6840ab5 typ=9 transaction=0 flags=00008000
The trace will often contain a systemstate dump, although most useful information is in the header section. Typically a session holding the row cache resource will either be on cpu or blocked by another session. If it is on cpu then errorstacks are likely to be required to diagnose, unless tuning can be done to reduce the enqueue hold time. Remember that on a RAC environment, the holder may be on another node and so multiple systemstates from each node will be required.
For each enqueue type, there are a limited number of operations that require each enqueue.
1) DC_TABLESPACES : This is probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
2) DC_SEQUENCES : Check for appropriate caching of sequences for the application requirements.
3) DC_USERS : Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.
4) DC_OBJECTS : Look for any object compilation activity which might require an exclusive lock and thus block online activity.
5) DC_SEGMENTS : This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.
6) In many cases no operations are notified in the trace file. Only the session ID is specified. In that case we need to investigate that session id.
The trace file tends to contain the following words:
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<
/opt/oracle/admin/EAIAPP/udump/eaiapp1_ora_23288.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/dbs
System name: Linux
Node name: db1-eai.prod.stl.cw.intraisp.com
Release: 2.6.18-128.4.1.el5
Version: #1 SMP Thu Sep 23 19:59:19 EDT 2010
Machine: x86_64
Instance name: EAIAPP1
Redo thread mounted by this instance: 1
Oracle process number: 43
Unix process pid: 23288, image: oracle@db1-eai.prod.stl.cw.intraisp.com
*** 2010-09-25 17:08:05.532
*** ACTION NAME:() 2010-09-25 17:08:05.532
*** MODULE NAME:(OMS) 2010-09-25 17:08:05.532
*** SERVICE NAME:(EAIAPP) 2010-09-25 17:08:05.532
*** SESSION ID:(120.29614) 2010-09-25 17:08:05.532
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000001b542d78, mode: N, request: S
row cache parent object: address=700000036f27328 cid=0(dc_tablespaces)
hash=a6840ab5 typ=9 transaction=0 flags=00008000
The trace will often contain a systemstate dump, although most useful information is in the header section. Typically a session holding the row cache resource will either be on cpu or blocked by another session. If it is on cpu then errorstacks are likely to be required to diagnose, unless tuning can be done to reduce the enqueue hold time. Remember that on a RAC environment, the holder may be on another node and so multiple systemstates from each node will be required.
For each enqueue type, there are a limited number of operations that require each enqueue.
1) DC_TABLESPACES : This is probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
2) DC_SEQUENCES : Check for appropriate caching of sequences for the application requirements.
3) DC_USERS : Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.
4) DC_OBJECTS : Look for any object compilation activity which might require an exclusive lock and thus block online activity.
5) DC_SEGMENTS : This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.
6) In many cases no operations are notified in the trace file. Only the session ID is specified. In that case we need to investigate that session id.
Index on a partitioned table waits on 'row cache lock' in RAC environment
Problem Description
Creating Index on a partitioned table fails with Oracle error ORA-04021. This is a 2-node Oracle RAC environment and in the table there is large number of partitions and sub-partitions. Index creation waits on 'row cache lock'. The same is true while dropping a tablespace. Drop Tablespace hangs on Row Cache Enqueue.
Problem Investigation
From the trace file we get following information.
It seems this process is waiting for exclusive enqueue lock mode on dc_segments:
Cause of the Problem
Index creation waits on 'row cache lock' in RAC environment occurred due to oracle bug.
Oracle named this bug as 'Bug 6321551' in version 10.2.0.2
'Bug 8417354' in version 10.2.0.4
'Bug 6004916' in version 11.0.0.0
Solution of the Problem
The bug is Oracle internal unpublished bug.
- This bug is fixed in Oracle version 10.2.0.5. So apply Oracle 10.2.0.5 patchset if you are running Oracle version 10g.
- If your Oracle database version is 10.2.0.2 or 10.2.0.3 you can apply one-off patch 6004916.
- Another workaround is to create the index with only one node being started (single instance).
i) Stop all RAC instance but one.
ii) Run the index creation script on one node.
iii) After process is completed startup rest of the instances.
Creating Index on a partitioned table fails with Oracle error ORA-04021. This is a 2-node Oracle RAC environment and in the table there is large number of partitions and sub-partitions. Index creation waits on 'row cache lock'. The same is true while dropping a tablespace. Drop Tablespace hangs on Row Cache Enqueue.
Problem Investigation
From the trace file we get following information.
PROCESS 24:Process 24 is executing sql: 0xacafc75e8 which is:
SO: 0xb9e505908, type: 4, owner: 0xb9f351708, flag: INIT/-/-/0x00
(session) sid: 477 trans: 0xb7ce39798, creator: 0xb9f351708, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0018-00000018, short-term DID: 0001-0018-00000019
txn branch: (nil)
oct: 9, prv: 0, sql: 0xacafc75e8, psql: 0xb0266cc18, user: 55/TA
O/S info: user: oracle, term: pts/1, ospid: 16322, machine: rac2
program: sqlplus@rac2 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'row cache lock' blocking sess=0x(nil) seq=4750 wait_time=0 seconds since wait started=18
cache id=2, mode=0, request=5
LIBRARY OBJECT HANDLE: handle=acafc75e8 mtx=0xacafc7718(1) cdp=1
name=CREATE INDEX USER_ACTIVITY_IDX ON PROD.USER_ACTIVITY(IMSI, CALLING_IMSI) TABLESPACE HISTORY_IDX_01 LOCAL PARALLEL 32 UNUSABLE
It seems this process is waiting for exclusive enqueue lock mode on dc_segments:
----------------------------------------From the trace file it is not clear who is holding this row cache enqueue.
SO: 0xafb5989f0, type: 50, owner: 0xb7ce39798, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=0xb9e505908 object=0xb004f3690, request=X
savepoint=0x25a3749
row cache parent object: address=0xb004f3690 cid=2(dc_segments)
hash=2997173a typ=11 transaction=(nil) flags=00000000
own=0xb004f3760[0xb004f3760,0xb004f3760] wat=0xb004f3770[0xafb598a20,0xafb598a20] mode=N
status=-/-/-/-/-/-/-/-/-
request=X release=FALSE flags=2
instance lock id=QC 1cd04d4c 3ab28c03
data=
0000006a 000001da 0039918b 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000
----------------------------------------
Cause of the Problem
Index creation waits on 'row cache lock' in RAC environment occurred due to oracle bug.
Oracle named this bug as 'Bug 6321551' in version 10.2.0.2
'Bug 8417354' in version 10.2.0.4
'Bug 6004916' in version 11.0.0.0
Solution of the Problem
The bug is Oracle internal unpublished bug.
- This bug is fixed in Oracle version 10.2.0.5. So apply Oracle 10.2.0.5 patchset if you are running Oracle version 10g.
- If your Oracle database version is 10.2.0.2 or 10.2.0.3 you can apply one-off patch 6004916.
- Another workaround is to create the index with only one node being started (single instance).
i) Stop all RAC instance but one.
ii) Run the index creation script on one node.
iii) After process is completed startup rest of the instances.
Subscribe to:
Posts (Atom)