Re: Oracle Advanced Replication
The only reason is that we have to change the primary key constraint. That's mean, we got to drop and recreate the primary key constraint. And if the replicated object is still in the replication group, it probably won't allow me to drop the primary key though. (or maybe I got it all wrong?) Thanks for your help anyway, Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ Paul Baumgartel .com>cc: Sent by: Subject: Re: Oracle Advanced Replication [EMAIL PROTECTED] 12/11/02 04:58 PM Please respond to ORACLE-L Is there some reason that you can't use DBMS_REPCAT.EXECUTE_DDL? See the Supplied PL/SQL Packages reference; here's the description: EXECUTE_DDL Procedure This procedure supplies DDL that you want to have executed at some or all master sites. You can call this procedure only from the master definition site. This package is intended to replicate changes made to objects that belong to a replication group, so it should be suitable for use here, as well as being a whole lot simpler than the approach you'd otherwise have to take. HTH. --- [EMAIL PROTECTED] wrote: > > To all, > > I have a 5-way multi-master replication set up on Oracle 817 and Sun > Solaris > 2.7. > > In the replication group, we would like to add a new column to a > replicated > table. > > Not only that, we would like to add this new column to the primary > key of this > replicated table. > > What are the steps that I would need to do? > > I seems to remember that if we add any new columns in the replicated > object, we > would need to > 1) quieced the whole environment > 2) drop this object from the replication group > 3) add the new column in the MDS as well as all other master sites > 4) add this table back to the replication group > 5) generate replication support > 6) resume replication again > > Would some replication expert tell me if I am correct or not? (I did > try to look > in the doc, but I can't find it any where) > > Thanks for your help! > > Winnie > > > -- > > <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ >(@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ >/ V \ Oracle Database Administrator`~`~ > o--m-m--o Infonet Services Corporation `~`~ ># mailto:[EMAIL PROTECTED]`~`~ > ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul
Oracle Advanced Replication
To all, I have a 5-way multi-master replication set up on Oracle 817 and Sun Solaris 2.7. In the replication group, we would like to add a new column to a replicated table. Not only that, we would like to add this new column to the primary key of this replicated table. What are the steps that I would need to do? I seems to remember that if we add any new columns in the replicated object, we would need to 1) quieced the whole environment 2) drop this object from the replication group 3) add the new column in the MDS as well as all other master sites 4) add this table back to the replication group 5) generate replication support 6) resume replication again Would some replication expert tell me if I am correct or not? (I did try to look in the doc, but I can't find it any where) Thanks for your help! Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: how to changes the sequence no for the redologs
Why do you want to reset the log sequence? The resetlog options only works after an incomplete recovery. Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ sarath kumar hoo.com> cc: Sent by: Subject: how to changes the sequence no for the redologs [EMAIL PROTECTED] 05/29/02 03:10 PM Please respond to ORACLE-L dear list, i have log sequence no like log%t_1_%s ie logTTM_1_001158967 logTTM_1_001158968 logTTM_1_001158969 logTTM_1_001158970 logTTM_1_001158971 logTTM_1_001158972. i can i reset the logs to logTTM_1_1 logTTM_1_2 and so on. i am on 7.3.4 when i give alter database open resetlogs it is not working. Sarath __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sarath kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How to drop a datafile from a tablespace quickly
Umm.. couple of things to verify... First, yes, it is me again! Second, the U-Turn was done on a yellow light (at least that is what I see that night!) Third, that is just a LA street, not a highway! :P Forth, this is not wild... just LA type of driving! Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Grabowy, Chris" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >cc: Sent by: Subject: RE: How to drop a datafile from a tablespace quickly [EMAIL PROTECTED] 05/17/02 09:58 AM Please respond to ORACLE-L Is that Winnie Liu?? The LA wild women street driver? Doing u-turns on a red light across a 4 lane highway?? Good to see you again. -Original Message- Sent: Friday, May 17, 2002 12:28 PM To: Multiple recipients of list ORACLE-L Tom, If you lost an archivelog from the time this datafile was created to present time. There is no way to simply offline that file and recover that file. It will become a classic case of performing incomplete recovery then. Glad that it works out for you! :D WInnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: Sent by: Subject: RE: How to drop a datafile from a tablespace quickly [EMAIL PROTECTED] 05/17/02 08:38 AM Please respond to ORACLE-L Thanks, Winnie! It works. I have a further quetion. If the archivelogs from time when the file was created to current were lost, do we still have someway to bring the file online? Tom -Original Message- Sent: Wednesday, May 15, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Umm.. Try this: 1) ALTER DATABASE DATAFILE 'filename' OFFLINE; 2) get rid of the offended datafile from OS 3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)'; 4) RECOVER DATAFILE 'filename'; 5) ALTER DATABASE DATAFILE 'filename' ONLINE; Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> om> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: RE: How to drop a datafile from a tablespace quickly 05/15/02 09:32 AM Since there is no data in the fi
RE: How to drop a datafile from a tablespace quickly
Tom, If you lost an archivelog from the time this datafile was created to present time. There is no way to simply offline that file and recover that file. It will become a classic case of performing incomplete recovery then. Glad that it works out for you! :D WInnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: Sent by: Subject: RE: How to drop a datafile from a tablespace quickly [EMAIL PROTECTED] 05/17/02 08:38 AM Please respond to ORACLE-L Thanks, Winnie! It works. I have a further quetion. If the archivelogs from time when the file was created to current were lost, do we still have someway to bring the file online? Tom -Original Message- Sent: Wednesday, May 15, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Umm.. Try this: 1) ALTER DATABASE DATAFILE 'filename' OFFLINE; 2) get rid of the offended datafile from OS 3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)'; 4) RECOVER DATAFILE 'filename'; 5) ALTER DATABASE DATAFILE 'filename' ONLINE; Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> om> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: RE: How to drop a datafile from a tablespace quickly 05/15/02 09:32 AM Since there is no data in the file, can we make a datafile to replace it? Tom Xie -Original Message- Sent: Wednesday, May 15, 2002 11:25 AM To: [EMAIL PROTECTED] There is absolutely NO WAY to drop a datafile from a tablespace at all. -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: Sent by: Subject: How to drop a datafile from a tablespace quickly [EMAIL PROTECTED] 05/15/02 09:58 AM Please respond to ORACLE-L Dear gurus: I just added a data file to a big tablespace (11GB) that has only one table. Unfortunately, when it was being backed up, the file head head corrupted. I don't have any backup of this file. I found that there is no data in this file yet. So I want to drop the file from the tablespace
RE: How to drop a datafile from a tablespace quickly
Umm.. Try this: 1) ALTER DATABASE DATAFILE 'filename' OFFLINE; 2) get rid of the offended datafile from OS 3) ALTER DATABASE CREATE DATAFILE 'filename(same as 1)'; 4) RECOVER DATAFILE 'filename'; 5) ALTER DATABASE DATAFILE 'filename' ONLINE; Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> om> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: RE: How to drop a datafile from a tablespace quickly 05/15/02 09:32 AM Since there is no data in the file, can we make a datafile to replace it? Tom Xie -Original Message- Sent: Wednesday, May 15, 2002 11:25 AM To: [EMAIL PROTECTED] There is absolutely NO WAY to drop a datafile from a tablespace at all. -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Xie, Tom" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> om> cc: Sent by: Subject: How to drop a datafile from a tablespace quickly [EMAIL PROTECTED] 05/15/02 09:58 AM Please respond to ORACLE-L Dear gurus: I just added a data file to a big tablespace (11GB) that has only one table. Unfortunately, when it was being backed up, the file head head corrupted. I don't have any backup of this file. I found that there is no data in this file yet. So I want to drop the file from the tablespace. As I know, to drop a data file, I have to export the data, drop the tablespace and recreate it, and then import data back to the tablespace. However, since our users can't stop using the table, I won't have enough down time to do that. Is there anyway I can quickly drop a data file from a tablespace? Don't tell me using "alter database datafile '...' offline drop" command. It won't work. I am working on Oracle 7.3.4. Thanks, Tom Xie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Xie, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Common Oracle RDBMS Misconceptions - standby db?
Also check out the notes on metalink: #90817.1. It states all the steps and concept clearly! Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~~``~ (@ @) @}-`-,-`-,--- Winnie Liu ---'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--o Infonet Services Corporation `~`~ # mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ mohammed bhatti To: Multiple recipients of list ORACLE-L .com>cc: Sent by: Subject: RE: Common Oracle RDBMS Misconceptions - root@fatcity.standby db? com 06/27/01 09:20 AM Please respond to ORACLE-L thank you, thank you, thank you... --- Jeremiah Wilton <[EMAIL PROTECTED]> wrote: > With graceful standby failover (I demo'd it last > year at OOW), you can switch > back and forth, back and forth as many times as you > want without recopying any > database. > > Basically, when you fail over to a standby, you shut > down the primary, apply all > the archived redologs to the standby, then copy all > the online logs and the > controlfile from the primary to the standby. People > who use incremental > checkpoints (DB_BLOCK_MAX_DIRTY_TARGET) must do a > 'create controlfile reuse > database noresetlogs' at this point. Other > people don't have to. > > Finally, you "recover database" to get the last one > or two online logs and open > the standby "noresetogs." The standby just picks up > the chain of SCNs where the > primary left off. > > The old primary can be immediately pressed into > service as a standby. Just > generate a standby controlfile on the new primary, > copy it into place on the old > primary and start it up as a standby database. > > You can go back and forth in this way as many times > as you want, and one just > picks up the chain of SCNs where the last one left > off. You never get a > divergence of changes. > > I have talked to people who found this out, and > looked like they were going to > cry, thinking of the countless hours they had spent > after every standby > failover, recopying to the standby to get it rollong > forward again. > > In 9i, they have an "automated" graceful failover > mechanism for standby > database. I haven't taken a look at it yet. > Probably it is a massive > java-based GUI that instantly consumes 512Mb or RAM. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Tue, 26 Jun 2001, Koivu, Lisa wrote: > > > OK. I admit my knowledge on standby is minimal, > having only read up on it, > > fiddled with it and used the idea sparingly for > migrations. > > > > However, Jeremiah, I'm very curious. You state > that 'Must reinstantiate > > standby after failover by recopying' is a > misconception. Yes, like many of > > the things you state below, the documentation does > say that - once you open > > a standby db in r/w mode, it is no longer a valid > standby after switching > > back to the primary. > > > > Can someone shed some light on why this is not > true? It seemed to make > > complete sense to me. I can see how opening a > database read only will work > > and not invalidate the standby, but r/w? > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jeremiah Wilton > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists >
RE: URGENT..not importing previous export!!!!!!!!
What kind of error you are getting from the import? Since you already have created the tablespaces and users in your new database, did you put "IGNORE=Y" when you perform the import? Winnie -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~``~ (@ @) @}-`-,-`-,--- Winnie Liu --',-'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--oInfonet Services Corporation `~`~ #mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "Gupta, Brijesh" <[EMAIL PROTECTED]> Sent by:cc: [EMAIL PROTECTED]Subject: RE: URGENT..not importing previous export 05/02/01 11:06 AM Please respond to ORACLE-L " import the exported files after making the same structue(tablespace and users) as there were in previous install." You don't have to create the users. The import will create it for you. Also in your case you don't have to create the database as the same path already exists as old database. It may be not importing because you have already created the users and when import file tries to create the user it fails and skips it. Try this -- create fresh 8.1.5 database -- run catexp.sql and import complete database. Brijesh -Original Message- Sent: Wednesday, May 02, 2001 2:46 AM To: Multiple recipients of list ORACLE-L hi all, there is an urgent problem that cropped up while i had some problems with oracle 8(version 8.0.4) database and after taking all the necessary backups, full database exports, physical copy of database and net8 folder.. deinstalled it. i then installed oracle 8i (version 8.1.5) and tried to import the exported files after making the same structue(tablespace and users) as there were in previous install. but there is not even a single table import taking place. will u pls help me in where i'm doing wrong. and how i could restore the earlier export files in this new database. here's some info that may help.. earlier version 8.0.4, sid orcl new version 8.1.5 , sid apex no/name of tablespace and users are exactly matching. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Fwd: please help
Sound to me the same thing had happened. He did not force a log switch after he finished back up all the datafiles to tape. If the required log (probably archive log) which is generated during the hot backup of the database can no longer be found. Part of that hot backup will be unusable. -- <\ />~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~``~ (@ @) @}-`-,-`-,--- Winnie Liu --',-'-,-'-,-{@`~`~ / V \ Oracle Database Administrator`~`~ o--m-m--oInfonet Services Corporation `~`~ #mailto:[EMAIL PROTECTED]`~`~ ~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~`~ "David A. Barbour" To: Multiple recipients of list ORACLE-L ntrix.net>cc: Sent by: Subject: Re: Fwd: please help [EMAIL PROTECTED] om 05/02/01 07:24 AM Please respond to ORACLE-L Bill, Did you force a log switch before and after the hot backup? Did you backup your archive logs (or alternatively, are they available)? Sounds to me like you need to run ALTER DATABASE RECOVER UNTIL (CANCEL, TIME, CHANGE, USING BACKUP CONTROLFILE); If you didn't force the log switch, be mindful that you may have to specify one or more of the redo logs to get the requested SCN. Did you backup the controlfile? redo logs? init.ora? orapasswd? These are some generalities, which may or may not apply to your specific situation. However, given the information at hand, it's probably where I'd start looking. Regards. David A. Barbour Oracle DBA, OCP Jonathan Gennick wrote: > > Fellow list members, I received the following email from a > reader a few minutes ago. If you skip down to where he talks > about backup, you'll see that he's in trouble with a > database that won't recover. I've already suggested that he > open a TAR, and that he supply more specifics as to error > messages and the like, but maybe someone on this list can > draw some conclusions from what he's told me so far. If > you're good at recovery, have a look at what he says. I'll > post his email address later if he says its ok, and I'll > pass on any advice/suggestions I receive in the meantime. > > -- > Best regards, > > Jonathan Gennick > mailto:[EMAIL PROTECTED] * 906.387.1698 > http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org > > Tuesday, May 01, 2001, 10:27:51 PM, X wrote: > BC> Hi Jonathan, > > BC> I always read and like your articles in the Oracle > BC> Magazine. > > BC> Could you please give me a moment of your time .. > BC> I have some couple of pressing problems and questions > BC> if you don't mind as i am under fire at work .. > > BC> I have trouble with some of the Oracle databases that > BC> i manage on Sun Solaris platform, sometimes i have my > BC> shutdowns hanging and this takes a very long time to > BC> complete. Also, hanging problems with some of the sql > BC> statements. Could you lend a hand about any possible > BC> clues as to what i should do or where i should look > BC> for answers. > > BC> My last question is about hotback. > > BC> I ran a hot backup yesterday and tried recovering > BC> today. I was faced with an Oracle error saying system > BC> tablespace needs more recovery and that open resetlogs > BC> will get an error after automatically applying the > BC> redo logs. > > BC> My backup strategy went this way, > > BC> i created a backup shell script and > BC> i put all tablespaces in hotbackup mode at the same > BC> time with this syntax.. > > BC> select 'alter tablesp
RE: How to get rid off file permanently?
No, you simply cannot drop a datafile from the tablespace. You will need to drop the whole tablespace and rebuild it. Winnie "udaycb" <[EMAIL PROTECTED]>@fatcity.com on 04/12/2001 11:48:56 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: try recreating the control file . -Original Message- Oracle Sent: Thursday, April 12, 2001 1:36 PM To: Multiple recipients of list ORACLE-L Hi all, I need to permanently get rid off some files from the database. I did this: 1. alter database datafile '/orafs/ora1/oradata/LUCP/users02.dbf' offline drop 2. rm the file from physical directory After I shutdown and restart the db, I still see the file in dba_data_files table. So how to tell the control file that the file is already gone? Thank you. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: udaycb INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: online backup query
In V$INSTANCE, there is a column named PARALLEL which will tell you whether the instance is in OPS mode or not. In online backup mode, you will need to backup 1) all datafiles 2) a binary backup of the controlfile 3) all archivelog files generated during the online backup period of time 4) I will also backup the init.ora file, a backup trace of the controlfile, password file etc. Winnie "Seema Singh" <[EMAIL PROTECTED]>@fatcity.com on 03/28/2001 12:25:55 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi gurus I am new to this group. What are those files need to backup in online backup mode? how we know the database is setup in OPS mode? Please help me. Thanks in advance. Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Db stat change query
Yes, it is necessary to take a full backup when you change your database from noarchivelog mode to archivelog mode. Or else you will never have a "base" to perform recovery if it is needed. Winnie "Seema Singh" <[EMAIL PROTECTED]>@fatcity.com on 03/28/2001 01:25:26 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi gurus Is it necessary to take full backup when we change the database from archivelog mode to noarchivelog mode and vice versa. Thanks -seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Update existing rows at import
Can you have a before-insert trigger on your table to do whatever you want and then use conventional sqlldr to load it? winnie "Helmut Daiminger" <[EMAIL PROTECTED]> on 03/28/2001 12:38:10 PM Please respond to <[EMAIL PROTECTED]> To: "Oracle DBA List \(Lazy DBA\) \(E-mail\)" <[EMAIL PROTECTED]>, "Oracle List \(Telelist\) \(E-mail\)" <[EMAIL PROTECTED]>, "Joe Sawyer \(E-mail\)" <[EMAIL PROTECTED]>, "Frach Karsten \(E-mail\)" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc: Hi! Is there a tool available that allows me to read data from a comma-delimited flat file into Oracle and if a row already exists, updates that record. If the row does not exist, it should insert a new record into the table. Looks to me like SQL*Loader can't do this. How could this be done? This is 8.1.6 on Win2k. Thanks, Helmut Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Problems with export/import
Maybe there are several things you want to check on your table in database B 1) Do you have any trigger on that table? 2) Do you have any foreign key constraints on that table? 3) Is that table structure the same on 2 databases? Winnie "Helmut Daiminger" <[EMAIL PROTECTED]> on 03/28/2001 11:24:28 AM Please respond to <[EMAIL PROTECTED]> To: "Oracle DBA List \(Lazy DBA\) \(E-mail\)" <[EMAIL PROTECTED]>, "Oracle List \(Telelist\) \(E-mail\)" <[EMAIL PROTECTED]> cc: Hi! I'm having weird problems with exporting/importing tables... First I run an export on database A About to export specified tables via Conventional Path ... Table(T) or Partition(T:P) to be exported: (RETURN to quit) > tbclocktimes . . exporting table TBCLOCKTIMES 2929 rows exported The exports terminates successfully without warnings. Then I want to import that dump file into database B, but it fails: . importing VIVOUSER's objects into VIVOUSER . . importing table "TBCLOCKTIMES" IMP-00058: ORACLE error 1403 encountered ORA-01403: no data found Any idea what's going wrong? This is 8.1.6 on Win2k Thanks, Helmut Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: changing db_block_size
export and import is your only solution to recreate the database in different db_block_size Winnie Roy Ferguson <[EMAIL PROTECTED]>@fatcity.com on 03/27/2001 04:40:22 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: all, I would like to change the db_block_size (currently 2048) of our production database and would be interested in hearing from those that have done this on a not so small database. This is our oracle financials/manufacturing server and is about 30GB in size. Did you recreate the database using imp/exp or another method? Running Oracle 8.0.5 on Sun Solaris 7. thanks in advance...roy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Roy Ferguson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: LOB ?
I am not quite sure that you can reference a LOB in a remote database through a database link. You are very likely to get the ORA-22992 error when you attempt to do so. 22992, 0, "cannot use LOB locators selected from remote tables" // *Cause: A remote LOB column cannot be referenced. // *Action: Remove references to LOBs in remote tables. Winnie "Gene Sais" <[EMAIL PROTECTED]>@fatcity.com on 03/27/2001 12:52:13 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: LOB question? Can you have db A have a view w/ db link reference to db B blob data type? I think so, but not 100% sure. The reason I ask is I have a 3rd party app that wants to move 500gb worth of images to a remote db, but have the current db reference it as if it was in the same db through a view. Thanks for any blob insight the list can offer. Gene Sais -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: DBMS_REPAIR package usage
Thanks for your help! I will definitely try that later today! :D It's really nice to have a full team of people helping me out when I am in a "confused" phrase. And I really do hope that there is not any "I know it's off-topic, but I just couldn't resist .." spam over there. I use my delete keys a lot more frequent than before (it is only my opinion. I am not starting any war here..) :P Winnie yong huang <[EMAIL PROTECTED]> on 03/24/2001 05:04:00 AM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Hi, Winnie, How did you find the file# to be 9 (unless you messed with your original error message)? I suggest you post your message to news:comp.databases.oracle.server (or http://news.interbulletin.com/cgi-bin/ibwrn/post/comp.databases.oracle.server if your company doesn't have a news server). Hopefully it will attract attention of Jonathan Lewis, the Oracle 8i expert, and several (former) Oracle employees such as Kyle Hailey, Howard Rogers and Anjo Kolk. The good thing about that newsgroup is nobody is audacious enough to post spam messages like "tomorrow I'll post the chocolate recipe here". Yong Huang [EMAIL PROTECTED] --- [EMAIL PROTECTED] wrote: > > > Yong, > > Thanks a lot for all the research! :D > > The file# that actaully contains this block is 9. My database is not that > big at all. > > I did do some research myself and some Oracle analysts in the World Wide > Support does suggest that the influxed blocks are very likely to be a > fractured block. But I reallly have no idea how it got in there... . > > Winnie > > > > > > yong huang <[EMAIL PROTECTED]> on 03/23/2001 04:01:21 PM > > To: [EMAIL PROTECTED] > cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] > > Subject: Re: DBMS_REPAIR package usage > > > Hi, Winnie, > > Just a little more research. I wonder how you can have an rdba that big, > 0x24070020, which is 604438560 in decimal. > > SQL> var a number; > SQL> exec :a := dbms_utility.data_block_address_file(604438560); > > PL/SQL procedure successfully completed. > > SQL> print > > A > - > 144 > > SQL> exec :a := dbms_utility.data_block_address_block(604438560); > > PL/SQL procedure successfully completed. > > SQL> print > > A > - >458784 > > This is done on 8.1.6. It says the block is in file 144, block 458784. Why > does > your error say file=0? Anyway, in case you do have a file numbered 144, > check > to see if there's an object there. If it's indeed file 0, the dba should be > the > same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS > can > confirm this. However, that file# 0 may be just an indicator that that > information is lost, as multiple other 0's look like. > > I believe dbv reports an error when it encounters a fractured block, i.e., > the > first two bytes of tail (0003 in your case) does not match the last two > bytes > of rdba (0020). We know how a fractured block is created during hot backup. > But > I don't understand why an offlined datafile (as you said in another email) > can > contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. > > Yong Huang > [EMAIL PROTECTED] > > you wrote: > > I have a datafile in my production box (a user data tablespace), when I run > dbv against it, it showed that 5 blocks are "influxed" > > Page 458784 is influx - most likely media corrupt > *** > Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. > Fractured block found during dbv: > Data in bad block - type:0. format:0. rdba:0x > last change scn:0x. seq:0x0 flg:0x00 > consistancy value in tail 0x0003c204 > check value in block header: 0x0, check value not calculated > spare1:0x0, spare2:0x0, spare2:0x0 > > We can copy this file to tape, dd this file. On the OS disk level, the OS > does > n > ot treat this as corrupted. But it is corrupted on the oracle > (software) level. > > I've checked and can't find any object associate with these 5 corrupted > blcok. > > That means that there is no data inside those blocks. > > Since the tablespace is about 12 GB on a highly active system (which only > got 3 > hours maintance window each month), export/import (then drop the > tablespace) > which Oracle support suggested is mostly out of the question. (Especially, > it > is > very hard for me to convince the sysadmin that the blocks are > corrupted > as they don't see any I/O error associate with this file and the developers > don' > t see any problem with the application either!) > > I am currently thinking about upgrading this database to 8.1.6 to make use > of > th > e DBMS_REPAIR package to make those blocks as "unusable". But I > am not sure that if the DBMS_REPAIR package can run against the blocks > which do > not belong to any objects!! Can someone give me some > guidences? > > thanks > > Winnie > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.y
Re: DBMS_REPAIR package usage
Yong, Thanks a lot for all the research! :D The file# that actaully contains this block is 9. My database is not that big at all. I did do some research myself and some Oracle analysts in the World Wide Support does suggest that the influxed blocks are very likely to be a fractured block. But I reallly have no idea how it got in there... . Winnie yong huang <[EMAIL PROTECTED]> on 03/23/2001 04:01:21 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL> var a number; SQL> exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL> print A - 144 SQL> exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL> print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: DBMS_REPAIR package usage
I do run the dbv against a down database. The same result happened. If FTP, copy are all okey, it means that the block is not a media corrupted block. It does not stop it from being a software corrupted block. If exp works fine, it only tells us that there is currently no data/object in that "corrupted block". Oracle support did tell me that if Oracle tried to create or reclaim the corrupted block, it will reformat the block. I cannot find out if it is true of not since it is impossible for me to force Oracle to create an object in those corrupted blocks! (too fragmented!) Winnie Mandar Ghosalkar <[EMAIL PROTECTED]>@fatcity.com on 03/22/2001 04:21:34 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: since does not change datablock u can run it against online datafiles. but it would report blocks as corrupted which are being changed. offline or shutdown is the best way. if u cant bring it down, try running analyze table validate structure cascade -Mandar > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 22, 2001 5:12 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: DBMS_REPAIR package usage > > > > I would not decide whether a block is corrupted or not, just using dbv > utility. dbv reports corruption, even when analyze, exp and FTS goes > through fine without any problem. dbv reported a data > dictionary corruption > in our case. We ran analyze, exp and FTS, no problem. But > still dbv was > reporting corruption even after the database was down. > > Further the database has to be down or the tablespace has to > be offline > normal for dbv to work somewhat correctly (?) > > Thanks > Riyaj "Re-yas" Shamsudeen > Certified Oracle DBA > i2 technologies www.i2.com > > > > > Winnie_Liu@in > > fonet.comTo: Multiple > recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > > root@fatcity.Subject: > DBMS_REPAIR package usage > com > > > > > > 03/22/01 > > 04:22 PM > > Please > > respond to > > ORACLE-L > > > > > > > > > > > > To all, > > I have a datafile in my production box (a user data > tablespace), when I run > dbv against it, it showed that 5 blocks are "influxed" > > Page 458784 is influx - most likely media corrupt > *** > Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. > Fractured block found during dbv: > Data in bad block - type:0. format:0. rdba:0x > last change scn:0x. seq:0x0 flg:0x00 > consistancy value in tail 0x0003c204 > check value in block header: 0x0, check value not calculated > spare1:0x0, spare2:0x0, spare2:0x0 > > We can copy this file to tape, dd this file. On the OS disk > level, the OS > does not treat this as corrupted. But it is corrupted on the oracle > (software) level. > > I've checked and can't find any object associate with these 5 > corrupted > blcok. > > That means that there is no data inside those blocks. > > Since the tablespace is about 12 GB on a highly active system > (which only > got 3 hours maintance window each month), export/import (then drop the > tablespace) > which Oracle support suggested is mostly out of the question. > (Especially, > it is very hard for me to convince the sysadmin that the blocks are > corrupted > as they don't see any I/O error associate with this file and > the developers > don't see any problem with the application either!) > > I am currently thinking about upgrading this database to > 8.1.6 to make use > of the DBMS_REPAIR package to make those blocks as "unusable". But I > am not sure that if the DBMS_REPAIR package can run against the blocks > which do not belong to any objects!! Can someone give me some > guidences? > > thanks > > Winnie > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in &g
DBMS_REPAIR package usage
To all, I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does not treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don't see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of the DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Metalink
I agree with you, Rodd. Ever since they force everybody to open the TAR in metalink, they should expect a huge growth in the number of TAR submitted per day. Before they introduce this function, in order to open a TAR, you probably have to wait on the phone for at lease 30 minutes to open the TAR. Most people who waited for over 30 minutes to open a TAR are the people with REAL ISSUE! Now, you can just logon to the web and open the TAR. It can be done in about 5 minutes (if the site is up and running!!). More people is going to abuse the iTAR features. I can't believe that the Oracle folks never thought about this before!! Winnie "Holman, Rodney" <[EMAIL PROTECTED]>@fatcity.com on 03/05/2001 12:55:34 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: UNEXPECTED GROWTH?? How can they not expect growth when they force ALL their TAR functions to be initiated via iTAR. As for acceptance of Metalink, isn't that sort of like saying you can use Metalink for all your support contacts or you can use Metalink. What choice did we have in "accepting" this? At least he was more descriptive of the problem and what was taking place to fix it. Rodd -Original Message- ... The site has experienced tremendous and unexpected growth in customer acceptance of Metalink, especially after we introduced the iTAR functionality. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Holman, Rodney INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
EMPTY_CLOB() call
Does anyone see error ORA-600 [19942] and ORA-600 [19944] when the user try to make the function call: EMPTY_CLOB() in and update statement to initialize the CLOB value? Is it a bug? Winnie OS: Solaris 2.6 Database: 8.0.5.2.1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: rollback segment (0))
Look like you have to tune your rollback segment now. 01594, 0, "attempt to wrap into rollback segment (%s) extent (%s) which is being freed" // *Cause: Undo generated to free a rollback segment extent is attempting // to write into the same extent due to small extents and/or too // many extents to free // *Action: The rollback segment shrinking will be rollbacked by the system; // increase the optimal size of the rollback segment. Winnie "Reddy, Gautam" <[EMAIL PROTECTED]>@fatcity.com on 02/22/2001 01:30:26 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Any clues on what this means. 154:Errors in file D:\OraDba\Admin\trsp\bdump\trspSMON.TRC: 155:ORA-01595: error freeing extent (1) of rollback segment (0)) 156:ORA-01594: attempt to wrap into rollback segment (0) extent (1) which is being freed Thx Gautam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Last Fri. of the Month ?
I think it should work: select decode(to_char(last_day(sysdate),'DAY'),'FRIDAY',sysdate, next_day((last_day(sysdate)-7),'FRIDAY')) from dual Replace sysdate with whatever date you want Winnie VIVEK_SHARMA <[EMAIL PROTECTED]> on 02/21/2001 11:51:05 AM To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: Given Any Date , How to Deduce the Date for the Last Fri. of the SAME Month ? Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: [Q] why application use system_2 rollback segment?
First of all, why can't the user application use system_2 rollback segment? The only rollback segment they won't use is the SYSTEM rollback segment. Winnie L <[EMAIL PROTECTED]> on 02/21/2001 02:44:00 PM To: [EMAIL PROTECTED], [EMAIL PROTECTED] cc: We have ORACLE 8.1.6 on SUN Solaris 2.7 server. Recently I check "alert.log" file and found following error message: ORA-1628: max # extents 121 reached for rollback segment SYSTEM_2 Failure to extend rollback segment 10 because of 1628 condition FULL status of rollback segment 10 set.A We have seperate Rollback tablespace for users to run application. The following is our rollback configuration: NAME OPTIMAL_KBSIZE_KB HI_WATER_KBEXTENDS -- -- -- --- -- ROLLBACK_1 5120 53125312 0 ROLLBACK_2 5120 53125312 0 ROLLBACK_3 5120 53125312 0 ROLLBACK_4 5120 53125312 0 ROLLBACK_5 5120 53125312 0 ROLLBACK_6 5120 53125312 0 ROLLBACK_7 5120 53125312 0 ROLLBACK_8 5120 53125312 0 SYSTEM 568 568 0 SYSTEM_277367736 0 This is first time on four years this database got this kind of error message. Can anyone tell me why user application will use "system_2" rollback segment? Thanks. Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: A strange space issue problem
It's time to look into the tablespace and see if it is real fragmented! Winnie "Richard Ji" <[EMAIL PROTECTED]>@fatcity.com on 02/16/2001 10:01:17 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi all, I am experiencing a problem and am in need of your opnions. I have a table which has two columns, one is a NUMBER, one is a LONG RAW. The LONG RAW column is used to store a Java Serialized Object of a size about 5k to 12k. We get about 15000 rows per day. Each night the old rows are purged to make room for the new ones. This worked fine for over 6 months, except once the table failed to extend because the purge job was broken and we didn't have enough space. But about a week ago, I am starting to see ORA-1653: unable to extent table message appear in alert log randomly. The purge job is running fine and did free up space. Only around 20 out of 15000 caused the error message. At first I suspect those failed ones must be trying to put in something BIG. But after the developer debugged it, he told me the size of the binary is only between 6-8k of the failed ones which is no larger than the others. So now I am really puzzled. I have one transaction that's trying to stick an 8k LONG ROW into the table and it failed with ORA-1653 and the next whole bunch of transactions going into the table successfully with the same size or even bigger. And there is no delete happened between those transactions. Nothing is deleted from the database except the nightly purge job which only deletes from that table. I suspect there is something that the developer didn't find out, but I want to rule out the possibility that it's the database that's causing the problem. Thanks in advance for any suggestions, comments, even if it's a shot in the dark. Richard Ji -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Need help in moving LOB index
Thanks a lot for the advise. I check it out in metalink. And they advice the same thing to me! :P hehe.. look like I have less jobs to do now! Winnie "MacGregor, Ian A." <[EMAIL PROTECTED]>@fatcity.com on 02/15/2001 02:36:08 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Oracle recommends against this. LOB_index_clause This clause is deprecated as of Oracle8i. Oracle generates anindex for each LOB column. The LOB indexes are system named and system managed, and reside in the same tablespace as the LOB data segments. It is still possible for you to specify this clause in some cases. However, Oracle Corporation strongly recommends that you no longer do so. I believe you can move the lobsegment along with its index with the modify lob storage clause of the alter table command. I haven't tried it. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 15, 2001 1:09 PM To: Multiple recipients of list ORACLE-L Hi all, I know that it is kind of a stupid question. But I've been struggling with the syntax to move the LOB index to a different tablespace. Would any one please kindly help me out on this part? Thanks!! :D Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Need help in moving LOB index
Hi all, I know that it is kind of a stupid question. But I've been struggling with the syntax to move the LOB index to a different tablespace. Would any one please kindly help me out on this part? Thanks!! :D Winnie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).