Scripts to reengineer a DB / recovery speed-up
Dear gurus ! Could you please point me to a free script /tool to capture the current DB metadata and to be able to recreate the DB (except for the actual data), i.e. tablespaces, users, roles etc... I can think of 2 semi-ways of doing it: 1) get the physical DB attributes (data files , redo logs , character set etc..) from alter database backup controlfile to trace 2) get the rest of metadata from a full DB export with no rows (rows=N). But if someone has a ready script , it will save me some time and work ;-) Thanks a lot ! Another question : i need the above in order to speed up bringing our DB back to production. We currently back it up via RMAN to tapes (via HP OmniBack II). This is a quite large DB (~950 GB, running on win2k , no UNIX) , and the RMAN restore/recovery took almost 48 hours after the recent crash. We're looking to speed up recovery and the most feasible option seems to be this : recreate the DB (empty DB , just build the users, TBS , tables etc..) , load configuration / reference / lookup/ static data (which needs to be backed up (exported) separately) . At this point the DB is operational again, loss of detailed data may be tolerated. The actual question is : what do you think of this scheme ? DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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).
ALTER TABLE DROP COLUMNS CONTINUE - how far has it gotten
A drop unused columns checkpoint was interrupted opn a large table. As a result the table is not readable while we are running ALTER TABLE DROP COLUMNS CONTINUE Is there any way to determine how far it has gotten? There is no entry in V$SESSION_LONGOPS. Thanks. Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: ALTER TABLE DROP COLUMNS CONTINUE - how far has it gotten
Jay, I've not played with that particular command lately, but it is likely that V$TRANSACTION can provide some info. For example, when I'm doing a TRUNCATE on a large table, I have found that the value in the column USED_UREC (i.e. number of undo records created) on V$TRANSACTION corresponded to the number of extents in the table being truncated. When I queried DBA_SEGMENTS to find the number of extents for the table, I found that the TRUNCATE finished when USED_UREC hits the same number as the number of extents, hence my assumption about the meaning of the value in USED_UREC. I don't know if you might be able to find similar info for a DROP COLUMNS command, but I'd guess that USED_UREC might correspond to the number of rows being modified by the DROP COLUMN, so having the original NUM_ROWS or COUNT(*) on the table might be helpful. Since you are doing a CONTINUE operation, even if this was true it might be difficult to gauge where you are currently since you probably don't know how many rows you had processed in the previous transaction(s)... For query purposes, V$SESSION.TADDR joins to V$TRANSACTION.ADDR... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, September 22, 2002 3:03 PM A drop unused columns checkpoint was interrupted opn a large table. As a result the table is not readable while we are running ALTER TABLE DROP COLUMNS CONTINUE Is there any way to determine how far it has gotten? There is no entry in V$SESSION_LONGOPS. Thanks. Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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.com -- Author: Tim Gorman 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: Scripts to reengineer a DB / recovery speed-up
Go to Cool-Tools website http://www.cool-tools.co.uk/ and have a look at the Databee DBA Tool Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Andrey Bronfin [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 23-09-2002 04:43 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Scripts to reengineer a DB / recovery speed-up Dear gurus ! Could you please point me to a free script /tool to capture the current DB metadata and to be able to recreate the DB (except for the actual data), i.e. tablespaces, users, roles etc... I can think of 2 semi-ways of doing it: 1) get the physical DB attributes (data files , redo logs , character set etc..) from alter database backup controlfile to trace 2) get the rest of metadata from a full DB export with no rows (rows=N). But if someone has a ready script , it will save me some time and work ;-) Thanks a lot ! Another question : i need the above in order to speed up bringing our DB back to production. We currently back it up via RMAN to tapes (via HP OmniBack II). This is a quite large DB (~950 GB, running on win2k , no UNIX) , and the RMAN restore/recovery took almost 48 hours after the recent crash. We're looking to speed up recovery and the most feasible option seems to be this : recreate the DB (empty DB , just build the users, TBS , tables etc..) , load configuration / reference / lookup/ static data (which needs to be backed up (exported) separately) . At this point the DB is operational again, loss of detailed data may be tolerated. The actual question is : what do you think of this scheme ? DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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). STG40831 Description: Binary data
RE: count(*)
Title: RE: count(*) Hi, I've read so many suggestions for making this count(*) quicker but we are all assuming that it is needed and has a purpose in the first place - which may or may not be the case. Obviously the quickest way to do the count(*) is to NOT do it in the first place. I'd still like to know the purpose of the count(*) and what verification it is performing? And also, why are there so many rows being inserted into the table. Cheers,Craig. -Original Message-From: Craig Munday [mailto:[EMAIL PROTECTED]]Sent: Friday, 20 September 2002 10:53 AMTo: Multiple recipients of list ORACLE-LSubject: RE: count(*) Hi, I am not sure if I have a solution for you, but I have a number of questions: 1) just for my interest, what type of application would need to insert 3-4 million records per hour? 2) Why does the application need to do the select count(*)? What verification is being performed? 3) Perhaps the design of the application could change so the number of records is reduced? Cheers, Craig.
RE: count(*)
Title: RE: count(*) Hi Depending on how the transactions are defined I do not think that you will be able to achieve the throughput as the single row will be a point of serialisation. Cheers, Craig. -Original Message- From: Naveen Nahata [mailto:[EMAIL PROTECTED]] Sent: Saturday, 21 September 2002 12:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: count(*) What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Mercadante, Thomas F 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,
RE: Stupid Bind Variable question
Title: RE: Stupid Bind Variable question Hi, I suggest the following: - If a literal is always going to be the same value than it is marginal whether you use a bind variable for this. - I'm not sure why you are using the to_char to_date functions? You should be able to create a bind variable of type date - hence you shouldn't need to worry about the format. Cheers, Craig. -Original Message- From: Alan Davey [mailto:[EMAIL PROTECTED]] Sent: Saturday, 21 September 2002 1:29 AM To: Multiple recipients of list ORACLE-L Subject: Stupid Bind Variable question In any given SQL statement, do I need (or should I :^) ) create a bind variable for every literal value even if it is one of the following cases: 1) substring in a substr function 2) date/time format mask used in to_char or to_date function 3) comparison value in a decode/case statement (not in the where clause) I've seen it mentioned to use bind variables when comparing a column to a literal in the where clause, but nothing specific about the above scenarios. Basically what I am asking is, do all literals need to be made bind variables? Thanks. -- Alan Davey [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey 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: count(*)
Title: RE: count(*) I think this kind of huge volume of insertions can only take place if the application is doing a batch processing, so the issue of contention should not come. if it is something like recording to page-hits or web-hits, then obviously the table will slow down the things a lot. i don't have experience of huge DBs but 3 million rows an hour means 72 million rows in a day, which means 2.1 billion rows a month! this means it is recording some kind of transactions mostly web hits on a very popular web-site. what logic will forcesomeone like to do a count(*) on such a table? my gut feeling is that count(*) must be unnecessary, and simply because it is available easily in Informix(according to the poster), the application has somehow found a reason to use it regards naveen -Original Message-From: Craig Munday [mailto:[EMAIL PROTECTED]]Sent: Monday, September 23, 2002 5:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: count(*) Hi Depending on how the transactions are defined I do not think that you will be able to achieve the throughput as the single row will be a point of serialisation. Cheers, Craig. -Original Message- From: Naveen Nahata [mailto:[EMAIL PROTECTED]] Sent: Saturday, 21 September 2002 12:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: count(*) What about having a separate table with a single column and a single row to store only the count, and increment and decrement it using a row trigger on Insert and deletes? that way select count(*) will be very fast, the only ovehead will of the trigger, which i think should be offset by the performance gained by the select. Regards naveen -Original Message- Sent: Friday, September 20, 2002 6:24 PM To: Multiple recipients of list ORACLE-L Rishi, Do records get deleted from this table? If not, you could simply add an additional column that gets populated by a sequence, add an index on that column, and select max() from that column. Even better, simply query 'select sequence_name,last_number from user_sequences' to get the last value used. You may need to check whether sequence caching makes a difference with this query. Otherwise, Dennis gave some good advice. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 19, 2002 5:04 PM To: Multiple recipients of list ORACLE-L Sent: Thursday, September 19, 2002 2:48 PM To: '[EMAIL PROTECTED]' Rishi - I've encountered this as well. I think the problem is the fact that you are pounding millions of rows into the table. When you ask for a count, Oracle won't give you an approximate answer, but insists on giving you a precise answer as of the moment you hit return. You are right, your query can actually slow performance. No, to my knowledge Oracle doesn't maintain a record of the number of rows in the table, my guess being that could become a performance bottleneck. My recommendation would be to ask very precisely what is to be achieved with the count. As you noticed, the count will lag reality by quite awhile. Perhaps the application could maintain the count. I have quite a few batch programs that will display a running counter. If only an approximate count is needed, there may be an alternate method, like looking at how many segments are used and calculating. Just some thoughts. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 19, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Hi Gurus, In one of our insert intensive application we are inserting around 3-4 million rows / hour. Also this app needs to do a count(*) of the tables every 10 minutes for verifying some application based logic. This is really killing us and it takes a lot of time. Can you please guide me to a direction ( built in functions or something similar). Actually this app is being ported from Informix. Informix can somehow keep a trak of the count(*) of a table in its header somewhere. And yes I have tries count(1) , count(indexed_column) etc. Thanks In Advance. R.h -- 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
