RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Why are you converting an RBS tablespace? If just an exercise, it's understandable. If for a production database, just create a new tablespace, new rollback segments, and then drop the old ones. Jared Guang Mei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/23/2003 10:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:convert RBS tablespace from DMT to LMT (Oracle 8173) steps? Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Yes, I can. But I want to call this tablespace RBS because I use this name in all my other dbs. Just try to be consistent with naming convension. Guang -Original Message- [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 1:54 PM To: Multiple recipients of list ORACLE-L Why are you converting an RBS tablespace? If just an exercise, it's understandable. If for a production database, just create a new tablespace, new rollback segments, and then drop the old ones. Jared Guang Mei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/23/2003 10:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:convert RBS tablespace from DMT to LMT (Oracle 8173) steps? Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Guang Yes, I noticed that after I posted my reply. Typically if this was a production system I would tend to create new rollback segments, online the new segments, offline the old segments, then change the init.ora. Sometime later I would drop the old segments and tablespace, maybe after a backup cycle has occurred. Now, you will say that is unnecessary, and technically you would be right. But one of the rules you learn by being a DBA for a long time is to never delete anything until you are absolutely certain you won't need it. Always have a rollback plan (no pun intended). It is also a good idea to back up the control file to trace anytime you make changes in database structure. With my method, if you have one of those oh crap moments, you can quickly recover. This may be overkill in this specific instance, but there are many other circumstances where you may end up being awfully glad you were a little cautious. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
I see. Thanks. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Guang Yes, I noticed that after I posted my reply. Typically if this was a production system I would tend to create new rollback segments, online the new segments, offline the old segments, then change the init.ora. Sometime later I would drop the old segments and tablespace, maybe after a backup cycle has occurred. Now, you will say that is unnecessary, and technically you would be right. But one of the rules you learn by being a DBA for a long time is to never delete anything until you are absolutely certain you won't need it. Always have a rollback plan (no pun intended). It is also a good idea to back up the control file to trace anytime you make changes in database structure. With my method, if you have one of those oh crap moments, you can quickly recover. This may be overkill in this specific instance, but there are many other circumstances where you may end up being awfully glad you were a little cautious. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
How will you make sure that there are no active segments in your current rollback segments before you remove the datafiles with the OS? Even after you take the rollback segments off-line, Oracle will continue to use them as long as they have an active segment. BTW --- One way to help to avoid the ORA-01555 when you are committing across an open cursor is to take the rollback segment that that cursor is using off-line. No one else will come in a step on the free segment but your session will continue to process just fine until you close the cursor. At least that's been my experience. DENNIS WILLIAMS DWILLIAMS To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @LIFETOUCH.COM cc: Sent by: Subject: RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps? ml-errors 07/23/2003 02:29 PM Please respond to ORACLE-L Guang Yes, I noticed that after I posted my reply. Typically if this was a production system I would tend to create new rollback segments, online the new segments, offline the old segments, then change the init.ora. Sometime later I would drop the old segments and tablespace, maybe after a backup cycle has occurred. Now, you will say that is unnecessary, and technically you would be right. But one of the rules you learn by being a DBA for a long time is to never delete anything until you are absolutely certain you won't need it. Always have a rollback plan (no pun intended). It is also a good idea to back up the control file to trace anytime you make changes in database structure. With my method, if you have one of those oh crap moments, you can quickly recover. This may be overkill in this specific instance, but there are many other circumstances where you may end up being awfully glad you were a little cautious. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Oracle will not let you drop a rollback segment if there are active transactions using it. However, it will allow you to offline the segment and no new transactions can use it. I don't recall the exact status in v$rollstat, but I think it may say pending offline. As for the commit across cursors, the minute you commit, your transaction has ended, even if the cursor is still open. When you issue the next statement, you start a new transaction, which should assign you to a new rbs. The one you were using is still offline. Of course, if you online/assign/offline the rbs after each commit, you are reusing the rbs. I have not tested this, so I may be wrong (Thoughts, Kirti?). However, you may step on your own free segment. Additionally, during the time period of the online/assign/offline, there is a chance that another transaction will be assigned to the rbs. Daniel Thomas Day wrote: How will you make sure that there are no active segments in your current rollback segments before you remove the datafiles with the OS? Even after you take the rollback segments off-line, Oracle will continue to use them as long as they have an active segment. BTW --- One way to help to avoid the ORA-01555 when you are committing across an open cursor is to take the rollback segment that that cursor is using off-line. No one else will come in a step on the free segment but your session will continue to process just fine until you close the cursor. At least that's been my experience.begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
I just finished this conversion by using Ron's method: looping through RBS00X (X from 1 to 6): alter rollback segment RBS00X offline; drop rollback segment RBS00X; create rollback segment RBS00X storage(initial 2048K next 2048K minextents 2 ) tablespace RBSlmt; alter rollback segment RBS00X online; select count(*) from all_objects where status = 'INVALID'; select SEGMENT_NAME ,STATUS from dba_rollback_segs; I did not encount any problems. Guang -Original Message- Daniel Fink Sent: Wednesday, July 23, 2003 3:25 PM To: Multiple recipients of list ORACLE-L Oracle will not let you drop a rollback segment if there are active transactions using it. However, it will allow you to offline the segment and no new transactions can use it. I don't recall the exact status in v$rollstat, but I think it may say pending offline. As for the commit across cursors, the minute you commit, your transaction has ended, even if the cursor is still open. When you issue the next statement, you start a new transaction, which should assign you to a new rbs. The one you were using is still offline. Of course, if you online/assign/offline the rbs after each commit, you are reusing the rbs. I have not tested this, so I may be wrong (Thoughts, Kirti?). However, you may step on your own free segment. Additionally, during the time period of the online/assign/offline, there is a chance that another transaction will be assigned to the rbs. Daniel Thomas Day wrote: How will you make sure that there are no active segments in your current rollback segments before you remove the datafiles with the OS? Even after you take the rollback segments off-line, Oracle will continue to use them as long as they have an active segment. BTW --- One way to help to avoid the ORA-01555 when you are committing across an open cursor is to take the rollback segment that that cursor is using off-line. No one else will come in a step on the free segment but your session will continue to process just fine until you close the cursor. At least that's been my experience. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Commits across open cursors are not a good idea and I do not advocate them. However, we live in an imperfect world and sometimes you just can't find a big enough 2x4 to catch the developer's attention. Off-lining the rbs that they are using may not be a certainty, and it may be just a placebo, but I have found that it can allow a PL./SQL proc that is doing commits across an open cursor to finish successfully. Daniel Fink daniel.fink To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @sun.comcc: Sent by: Subject: Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps? ml-errors 07/23/2003 03:24 PM Please respond to ORACLE-L Oracle will not let you drop a rollback segment if there are active transactions using it. However, it will allow you to offline the segment and no new transactions can use it. I don't recall the exact status in v$rollstat, but I think it may say pending offline. As for the commit across cursors, the minute you commit, your transaction has ended, even if the cursor is still open. When you issue the next statement, you start a new transaction, which should assign you to a new rbs. The one you were using is still offline. Of course, if you online/assign/offline the rbs after each commit, you are reusing the rbs. I have not tested this, so I may be wrong (Thoughts, Kirti?). However, you may step on your own free segment. Additionally, during the time period of the online/assign/offline, there is a chance that another transaction will be assigned to the rbs. Daniel Thomas Day wrote: How will you make sure that there are no active segments in your current rollback segments before you remove the datafiles with the OS? Even after you take the rollback segments off-line, Oracle will continue to use them as long as they have an active segment. BTW --- One way to help to avoid the ORA-01555 when you are committing across an open cursor is to take the rollback segment that that cursor is using off-line. No one else will come in a step on the free segment but your session will continue to process just fine until you close the cursor. At least that's been my experience. Attachment Removed : daniel.fink.vcf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Hi, guang: I have two questions: 1. Why create rbs on lmt tablespace and later drop it again? Just do step 1-7 and it is ok. Or maybe you need to update your init file, but you can also offline old rbs01 and create new rbs01 on lmt, and so on. 2. If you have dmt rollback segment in production and you did create the rbs with(initial=next, min=20), in most case , rbs won't expand or shrink.And the convertion does not help much . right? Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 2:14 AM Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
Hi! Great you got everything working fine. Just a note, that in truly high available systems, it's reasonable to wait until all long-running queries, which were started before offlining rollback segments, have ended before you actually drop the offlined rollback segments. Oracle is able to read offline rollback segments for read consistency, but if you drop them, you might get ORA-1555's during your operation and you definitely don't want to see those in HA system. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 10:54 PM I just finished this conversion by using Ron's method: looping through RBS00X (X from 1 to 6): alter rollback segment RBS00X offline; drop rollback segment RBS00X; create rollback segment RBS00X storage(initial 2048K next 2048K minextents 2 ) tablespace RBSlmt; alter rollback segment RBS00X online; select count(*) from all_objects where status = 'INVALID'; select SEGMENT_NAME ,STATUS from dba_rollback_segs; I did not encount any problems. Guang -Original Message- Daniel Fink Sent: Wednesday, July 23, 2003 3:25 PM To: Multiple recipients of list ORACLE-L Oracle will not let you drop a rollback segment if there are active transactions using it. However, it will allow you to offline the segment and no new transactions can use it. I don't recall the exact status in v$rollstat, but I think it may say pending offline. As for the commit across cursors, the minute you commit, your transaction has ended, even if the cursor is still open. When you issue the next statement, you start a new transaction, which should assign you to a new rbs. The one you were using is still offline. Of course, if you online/assign/offline the rbs after each commit, you are reusing the rbs. I have not tested this, so I may be wrong (Thoughts, Kirti?). However, you may step on your own free segment. Additionally, during the time period of the online/assign/offline, there is a chance that another transaction will be assigned to the rbs. Daniel Thomas Day wrote: How will you make sure that there are no active segments in your current rollback segments before you remove the datafiles with the OS? Even after you take the rollback segments off-line, Oracle will continue to use them as long as they have an active segment. BTW --- One way to help to avoid the ORA-01555 when you are committing across an open cursor is to take the rollback segment that that cursor is using off-line. No one else will come in a step on the free segment but your session will continue to process just fine until you close the cursor. At least that's been my experience. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?
On Wed, 23 Jul 2003, zhu chao wrote: Hi, guang: I have two questions: 1. Why create rbs on lmt tablespace and later drop it again? Just do step 1-7 and it is ok. Or maybe you need to update your init file, but you can also offline old rbs01 and create new rbs01 on lmt, and so on. You are right that I only need to create rbs01 on lmt ts once, and that was how I did it (see my other post). 2. If you have dmt rollback segment in production and you did create the rbs with(initial=next, min=20), in most case , rbs won't expand or shrink.And the convertion does not help much . right? I set storage(initial 2048K next 2048K minextents 2 ) for all my rbs. So there will be expand or shrink in RBS tablespace. I think that convert to LMT would reduce space management operation in data dictionary, therefore help the performance. However, I have never done any measurements of how much improvement one could get by converting RBS tablespace from DMT to LMT. Guang Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 2:14 AM Do I need to? I thought after all the steps I still have the same rollback segment names (RBS01-06). The only difference is they will be in LMT now. Guang -Original Message- DENNIS WILLIAMS Sent: Wednesday, July 23, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Guang - Don't forget to update your init.ora! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi: I need to convert RBS tablespace (used for rollback segments) from DMT to LMT on a 24x7 production machine (Solaris 2.8, Oracle 8173). Here are the steps I am thinking of taking: 1. create a new rbslmt tablespace 2. create new rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) in rbslmt ts , set them online 3. alter all old rollback segments (RBS01, ... RBS06) offline; 4. Drop all old rollback segments; 5. alter old RBS tablespace offline 6. Drop old RBS tablespace 7. Remove old RBS tablespace's datafiles from OS 8. Create new RBS tablespace as LMT 9. Create new rollback segments (RBS01, ... RBS06) in RBS, set them online; 10. alter rollback segments (RBSlmt01, RBSlmt02, ... RBSlmt 06) offline, then drop them; 11. alter tablespace rbslmt offline 12. drop tablespace rbslmt 13. Remove tablespace rbslmt's datafiles from OS. Anything I am missing? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
