Re: Redo logs lost, old backups
Hi Maser, I am not sure this work or not. Actually my suggestion is call oracle support before you do anything. Just for your reference. 1) BACKUP the current situation with the database closed. The instructions here are destructive. You are STRONGLY advised to backup the current situation before proceeding. If you do NOT do this you may lose the chance to try other options. 2) If your datafiles are from different points in time it is best to try to use system tablespace files at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database. 3) Edit your initSID.ora file and set: _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS = list of all rollback segments Comment out any 'ROLLBACK_SEGMENTS= ' clause. 4) Invoke either server manager (svrmgrl) or SQL*DBA and issue the commands below: connect internal startup mount select * from v$datafile; ... Check here that all files you want to open with are listed as ONLINE (or as SYSTEM). If not: ALTER DATABASE DATAFILE 'full_path_to_file' ONLINE; until all required files are listed as online. ... RECOVER DATABASE UNTIL CANCEL; or RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; At the prompt enter the word: Cancel ALTER DATABASE OPEN RESETLOGS; 5) If this works you should attempt to export the database IMMEDIATELY. Once you have an export the database must be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch. A database which has been opened in this way, but not rebuilt will not be supported by Oracle. Any delay in extracting the contents, or any attempt to use the system may cause irreparable damage. NOTE: Be sure to remove the init.ora parameters added in step 3 otherwise you may accidentally corrupt any new database created using the same init.ora file. 6) It is possible that the OPEN RESETLOGS may fail with an error, or that accessing the data (eg: using export) may fail with an error. In this case note down the exact error and identify any trace files produced then contact Oracle Support Services with this information. Depending on the errors it may be possible to proceed further. Joan Name: arsqaALRT.LOG arsqaALRT.LOG Type: Text Document Maser, Donna (SEA) wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) 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
RE: Redo logs lost, old backups
Please provide an update as to whether this worked or not, I would be interested in knowing. Thanks, Ethan -Original Message- Sent: Wednesday, December 19, 2001 10:15 AM To: Multiple recipients of list ORACLE-L Hi Maser, I am not sure this work or not. Actually my suggestion is call oracle support before you do anything. Just for your reference. 1) BACKUP the current situation with the database closed. The instructions here are destructive. You are STRONGLY advised to backup the current situation before proceeding. If you do NOT do this you may lose the chance to try other options. 2) If your datafiles are from different points in time it is best to try to use system tablespace files at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database. 3) Edit your initSID.ora file and set: _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS = list of all rollback segments Comment out any 'ROLLBACK_SEGMENTS= ' clause. 4) Invoke either server manager (svrmgrl) or SQL*DBA and issue the commands below: connect internal startup mount select * from v$datafile; ... Check here that all files you want to open with are listed as ONLINE (or as SYSTEM). If not: ALTER DATABASE DATAFILE 'full_path_to_file' ONLINE; until all required files are listed as online. ... RECOVER DATABASE UNTIL CANCEL; or RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; At the prompt enter the word: Cancel ALTER DATABASE OPEN RESETLOGS; 5) If this works you should attempt to export the database IMMEDIATELY. Once you have an export the database must be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch. A database which has been opened in this way, but not rebuilt will not be supported by Oracle. Any delay in extracting the contents, or any attempt to use the system may cause irreparable damage. NOTE: Be sure to remove the init.ora parameters added in step 3 otherwise you may accidentally corrupt any new database created using the same init.ora file. 6) It is possible that the OPEN RESETLOGS may fail with an error, or that accessing the data (eg: using export) may fail with an error. In this case note down the exact error and identify any trace files produced then contact Oracle Support Services with this information. Depending on the errors it may be possible to proceed further. Joan Name: arsqaALRT.LOG arsqaALRT.LOG Type: Text Document Maser, Donna (SEA) wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
Re: Redo logs lost, old backups
Post, Actually I did try. database was opened successfully, but when I query dba_data_file, all the files are disabled. (RBS from recover status to disabled, other files from read/write to disabled ) From that point, I stoped (I don't have time) I am waiting somebody else to tell me. Joan Post, Ethan wrote: Please provide an update as to whether this worked or not, I would be interested in knowing. Thanks, Ethan -Original Message- Sent: Wednesday, December 19, 2001 10:15 AM To: Multiple recipients of list ORACLE-L Hi Maser, I am not sure this work or not. Actually my suggestion is call oracle support before you do anything. Just for your reference. 1) BACKUP the current situation with the database closed. The instructions here are destructive. You are STRONGLY advised to backup the current situation before proceeding. If you do NOT do this you may lose the chance to try other options. 2) If your datafiles are from different points in time it is best to try to use system tablespace files at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database. 3) Edit your initSID.ora file and set: _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS = list of all rollback segments Comment out any 'ROLLBACK_SEGMENTS= ' clause. 4) Invoke either server manager (svrmgrl) or SQL*DBA and issue the commands below: connect internal startup mount select * from v$datafile; ... Check here that all files you want to open with are listed as ONLINE (or as SYSTEM). If not: ALTER DATABASE DATAFILE 'full_path_to_file' ONLINE; until all required files are listed as online. ... RECOVER DATABASE UNTIL CANCEL; or RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; At the prompt enter the word: Cancel ALTER DATABASE OPEN RESETLOGS; 5) If this works you should attempt to export the database IMMEDIATELY. Once you have an export the database must be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch. A database which has been opened in this way, but not rebuilt will not be supported by Oracle. Any delay in extracting the contents, or any attempt to use the system may cause irreparable damage. NOTE: Be sure to remove the init.ora parameters added in step 3 otherwise you may accidentally corrupt any new database created using the same init.ora file. 6) It is possible that the OPEN RESETLOGS may fail with an error, or that accessing the data (eg: using export) may fail with an error. In this case note down the exact error and identify any trace files produced then contact Oracle Support Services with this information. Depending on the errors it may be possible to proceed further. Joan Name: arsqaALRT.LOG arsqaALRT.LOG Type: Text Document Maser, Donna (SEA) wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify
RE: Redo logs lost, old backups
Yes. There are ways to recover the database (of course , unsupported) . let me know the following details by offline, I will be able to help you. What is the current state of the database? What is the size of the redo logs? Was the last shutdown (if the database is down) is immediate or abort? Which version of Oracle and OS? Do you have the trace of the control file ? Do you know the locations of ALL datafiles? Do you have the alert log in place? Best Regards, K Gopalakrishnan (408) 934 9310 -Original Message- (SEA) Sent: Monday, December 17, 2001 8:10 PM To: Multiple recipients of list ORACLE-L HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Redo logs lost, old backups
how much money do you want to spend? If you are willing to spend a lot (like $5K for every 8 hours with a minimum of 16 hours -- price might have gone up, it was that about 3 years ago) call Tech Support, ask to speak to someone in Field Support about Data Unloader. This is what it does perfectly -- mines the existing datafiles and generates either sqlloader flat file input (if you don't have LONG columns) or export dumps that can be imported. You can restore using one of the old backups and truncate every table, then use the Data Unloader outputs to recreate. it will take time. Took me about 48 hours (some of which was spent waiting for people to arrive) from the time my database crashed because the redo logs were corrupted to the time we were back up and running. if you want more details, email me offlist. Rachel --- Maser, Donna (SEA) [EMAIL PROTECTED] wrote: HELP! I got hit with something or someone today and all the redologs for 2 of my production databases disappeared. I have no idea how, and will leave it to the SysAdmin to figure out what happened and how to prevent it from happening again. I am not in archivelog mode and normally take nightly cold backups. If at all possible, I need to recover these databases. The other problem is that my backup script was broken and (during my absence, I should never have taken a 2 week honeymoon!) no backups were taken. So, I'm working with 2 week old cold backup, and wondered if anyone knows a way (unsupported, of course) to recover the tablespaces that hold data with the old system datafile? I have tried all methods of recovery I could come up with to get the database to start without the logfiles, (using recover ... using backup controlfile, rebuilding controlfile) ... but always end up with a message that the system tablespace needs media recovery. I took a cold backup of the mess before I started tinkering with it, and I'm willing to try anything. Any ideas? The information contained in this email is intended for the personal and confidential use of the addressee only. It may also be privileged information. If you are not the intended recipient then you are hereby notified that you have received this document in error and that any review, distribution or copying of this document is strictly prohibited. If you have received this communication in error, please notify Celltech Group immediately on: +44 (0)1753 534655, or email '[EMAIL PROTECTED]' Celltech Group plc 216 Bath Road, Slough, SL1 4EN, Berkshire, UK Registered Office as above. Registered in England No. 2159282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maser, Donna (SEA) 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). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
