FAQ
Hi,
I ended up with a crashed Oracle db, which refuses to start. I have all the
datafiles for this database and was wondering if it is possible to somehow
"Load / Import" the data in these datafiles into another working database.
If yes, it will save me quite a lot of re-work. Can somebody help me on
this please ....

--
Regards,
Srinivas Chintamani

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Jack van Zanen at Apr 25, 2008 at 1:16 pm
    you will have to provide more details than that for a better answer, but
    here is one

    Yes you can most likely recover your data using DUL, contact oracle support
    for this.
    But there might be different solutions for your problem, you'll have to post
    more info though. Version, errors etc etc

    Jack
    On 25/04/2008, Srinivas Chintamani wrote:

    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all
    the datafiles for this database and was wondering if it is possible to
    somehow "Load / Import" the data in these datafiles into another working
    database. If yes, it will save me quite a lot of re-work. Can somebody
    help me on this please ....

    --
    Regards,
    Srinivas Chintamani
    --
    J.A. van Zanen

    --
    http://www.freelists.org/webpage/oracle-l
  • Srinivas Chintamani at Apr 25, 2008 at 1:31 pm
    Hi,
    The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem. What I am trying to do is to
    find out if, it is possible to load the data from the datafiles of the
    crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup / restore the
    db. Just take a backup, dump the backup file anywhere on the filesystem and
    point to SQL Server, where the backup file is at and it happily recovered
    the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.
    On Fri, Apr 25, 2008 at 6:50 PM, David Pintor wrote:

    Hi,

    I'm not really sure if there's a way of doing that, but before thinking
    about this I would try to recover the database first, or if your datafiles
    are in a consistent state, I would try to recreate the control file and try
    to restart...

    Good luck.

    David

    On 25/04/2008, Srinivas Chintamani wrote:

    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all
    the datafiles for this database and was wondering if it is possible to
    somehow "Load / Import" the data in these datafiles into another working
    database. If yes, it will save me quite a lot of re-work. Can somebody
    help me on this please ....

    --
    Regards,
    Srinivas Chintamani
    --
    Regards,
    Srinivas Chintamani

    --
    http://www.freelists.org/webpage/oracle-l
  • Jost, at Apr 25, 2008 at 1:38 pm

    Am Freitag, den 25.04.2008, 19:01 +0530 schrieb Srinivas Chintamani:
    Hi,
    The crashed database is deleted now and I only have the datafiles of
    the crashed database somewhere on my filesystem. What I am trying to
    do is to find out if, it is possible to load the data from the
    datafiles of the crashed database into another new database.
    Hi,

    if you have _all_ datafiles, it is no problem to create a new database
    out of this files. But if this files are not consistent, youre in
    trouble.

    It the are consistent, you can create new controlfiles and open the new
    Database with the Datefiles after a alter database open resetlogs
    Earlier when working with SQL Server, it was simple to backup /
    restore the db. Just take a backup, dump the backup file anywhere on
    the filesystem and point to SQL Server, where the backup file is at
    and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...
    It is no problem to restore a oracle database, if one knows what to
    do;-) No offense, it is the same as everywhere i think.

    As often you have to report more details to get good help.

    by

    Jörg
  • Toon Koppelaars at Apr 25, 2008 at 1:43 pm
    "which refuses to start"
    --> What is the error that you receive on startup?

    "The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem"
    --> If I read this carefully you are contradicting yourself here.... not?
    On 4/25/08, Srinivas Chintamani wrote:

    Hi,
    The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem. What I am trying to do is to
    find out if, it is possible to load the data from the datafiles of the
    crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup / restore the
    db. Just take a backup, dump the backup file anywhere on the filesystem and
    point to SQL Server, where the backup file is at and it happily recovered
    the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.

    On Fri, Apr 25, 2008 at 6:50 PM, David Pintor
    wrote:
    Hi,

    I'm not really sure if there's a way of doing that, but before thinking
    about this I would try to recover the database first, or if your datafiles
    are in a consistent state, I would try to recreate the control file and try
    to restart...

    Good luck.

    David

    On 25/04/2008, Srinivas Chintamani wrote:

    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all
    the datafiles for this database and was wondering if it is possible to
    somehow "Load / Import" the data in these datafiles into another working
    database. If yes, it will save me quite a lot of re-work. Can somebody
    help me on this please ....

    --
    Regards,
    Srinivas Chintamani



    --
    Regards,
    Srinivas Chintamani
    --
    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
    Toon Koppelaars
    RuleGen BV
    +31-615907269
    toon_at_rulegen_dot_com
    www_dot_rulegen_dot_com

    Author: "Applied Mathematics for Database Professionals"

    --
    http://www.freelists.org/webpage/oracle-l
  • Srinivas Chintamani at Apr 25, 2008 at 2:02 pm
    "which refuses to start"
    --> What is the error that you receive on startup?
    The error it __gave__ was "UNDOTbs1.dbf size X is smaller than Y
    blocks".

    "The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem"
    --> If I read this carefully you are contradicting yourself here.... not?
    After trying to get the database to start for about a full day, we
    just did a file system backup of all the files belonging to the crashed db
    and later on went on to create a new db with the same name. We couldn't
    change the name of the db, because a lot of developers were on this crashed
    db :(

    Regards,
    Srinivas.
  • David Barbour at Apr 25, 2008 at 2:22 pm
    Gee - I'm not sure what I can add to this with the exception that you are
    very lucky this is a development database and not production. Since that's
    the case, you ought to take the opportunity to practice your recovery
    techniques. I really don't know if you have a recoverable database or not,
    but I suspect, if you've got a full filesystem copy of all the datafiles,
    you might have a shot at it if you approach it correctly. Do you have
    access to Metalink and/or Oracle Support? If so, you might want to put in a
    service request and look through the notes. Additionally, you need to look
    at the backup and recovery documentation. Since we don't know what version
    of Oracle you're using, or anything else, go to tahiti.com and find the
    documentation for the version you're using. Then come up with a plan and
    post it here for more input.

    It might have been a good idea to ask for help before you decided to
    "delete" the database.
    On 4/25/08, Srinivas Chintamani wrote:

    "which refuses to start"
    --> What is the error that you receive on startup?
    The error it __gave__ was "UNDOTbs1.dbf size X is smaller than Y
    blocks".

    "The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem"
    --> If I read this carefully you are contradicting yourself here.... not?
    After trying to get the database to start for about a full day, we
    just did a file system backup of all the files belonging to the crashed db
    and later on went on to create a new db with the same name. We couldn't
    change the name of the db, because a lot of developers were on this crashed
    db :(

    Regards,
    Srinivas.
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Apr 25, 2008 at 4:49 pm
    ".we just did a file system backup of all the files belonging to the crashed
    db"



    If you actually have all the files belonging to the crashed db, you just
    need to reload them and start the database.



    Of course you also wrote: "After trying to get the database to start for
    about a full day,." with no indication of what was wrong at that point.



    For all we know you lost a disk drive. We have no idea what Oracle was
    telling you, either interactively or through the alert logs and dump files,
    about what was wrong with the database or system at that point. IF the files
    were corrupted you'll need to go back to an older backup set and roll
    forward.



    Backing up and recovering Oracle databases is simple and has been since at
    least 1988, including the ability to recover from a complete set of files to
    any point in time equal to or future to the present from the youngest of the
    files of the available fileset, even if hetereochronus component files must
    be used due to lost media in some backup sets.



    Apparently your database was down when you made a file system backup. Your
    likely starting point is to reload that. If you didn't get all the files
    though, or if your files were corrupt already when you made the backup the
    best you can do is one of the datafile unloaders, either from Oracle or at
    least two fine consultant/vendors you can find on this list's archives.



    By the way, your statement "We couldn't change the name of the db, because a
    lot of developers were on this crashed db :( " makes no sense at all.
    Recreating Oracle databases of several different names and connecting to
    similar schema within them is merely a question of network addresses and
    instance name connections together with user names and passwords. No
    application in Oracle really needs to know the name of the database it is
    connecting to except for maintenance utilities like RMAN or OEM Grid that
    may need to know the name to keep multiple track of multiple databases,
    instances, and the components thereof.



    Finally, there is actually a way to add files from one oracle database to
    another. That is called "transportable tablespaces" and it does require
    specific advanced planning so that the metadata is prepared for the database
    dictionary to be made aware of what is in the files to be added. It allows
    the exchange of an arbitrarily large volume of data between databases at a
    cost only proportional to the amount of the metatdata required to describe
    the contents. I'll go out on a limb here and guess you're unlikely to have
    prepared your tablespaces for transport.



    Regards,



    mwf

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Srinivas Chintamani
    Sent: Friday, April 25, 2008 10:03 AM
    To: Toon Koppelaars
    Cc: oracle-l
    Subject: Re: Is it possible to add existing datafiles to an oracle database?



    "which refuses to start"

    --> What is the error that you receive on startup?
    The error it __gave__ was "UNDOTbs1.dbf size X is smaller than Y
    blocks".

    "The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem"

    --> If I read this carefully you are contradicting yourself here.... not?
    After trying to get the database to start for about a full day, we
    just did a file system backup of all the files belonging to the crashed db
    and later on went on to create a new db with the same name. We couldn't
    change the name of the db, because a lot of developers were on this crashed
    db :(

    Regards,
    Srinivas.
  • Hemant K Chitale at Apr 26, 2008 at 8:07 am
    "Just take a backup, ...."
    Even in Oracle it is very easy . You need to know which files to backup.
    However, I believe that you had posted this on forums.oracle.com as well.
    You had a datafile where the filesize in the header doesn't match the size on
    the filesystem -- possibly an instance abort / server failure while
    the file was
    being resized.
    At 09:31 PM Friday, Srinivas Chintamani wrote:
    Hi,
    The crashed database is deleted now and I only have the datafiles of
    the crashed database somewhere on my filesystem. What I am trying
    to do is to find out if, it is possible to load the data from the
    datafiles of the crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup /
    restore the db. Just take a backup, dump the backup file anywhere
    on the filesystem and point to SQL Server, where the backup file is
    at and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.
    Hemant K Chitale
    http://hemantoracledba.blogspot.com

    "A 'No' uttered from the deepest conviction is better than a 'Yes'
    merely uttered to please, or worse, to avoid trouble."
    Mohandas Gandhi Quotes
    : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
  • Srinivas Chintamani at Apr 28, 2008 at 3:42 am
    Hi,
    The post to forums.oracle.com is not mine. Having read through the said
    post, I thought I might get some ideas from more experienced DBAs help solve
    my issue. I am TOTALLY new to Oracle backup/recovery, its something that
    got pushed onto me ...

    Anyways, let me try an explain my situation in a little bit more detail ...

    Ours is an Agile shop and each developer gets his/her own schema on the db.

    We have an imp dump, that gets loaded into a new schema, whenever a new
    developer joins the team. Each new schema gets its own tablespace - each
    tablespace consisting of two datafiles each.

    Once the db was setup (on a windows 2003 standard server) a few months ago,
    more and more developers added their own schemas into the db - the count
    being about 40 (fourty) schemas when the db crashed.

    The db refused to startup, since the UNDO tablespace file was smaller than
    what Oracle expected. The error was:

    Error Start ---------------
    ORA-01122: database file 2 failed verification check
    ORA-01110: data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF'
    ORA-01200: actual file size of 83976 is smaller than correct size of 138240
    blocks
    Error End -----------------

    I think that all the other datafiles are ok and was thinking if it were
    possible at all to take some schema's datafiles and load them up into some
    other database.

    I had logged an SR with Oracle support, which they wanted us to bump to
    SEV-1 (after someone from Oracle support tried to bring the db up over
    CollabSuite for about four/five hours). Since I had already spent about a
    day trying to get this thing to come up, we could not afford to loose any
    more time, hence we deleted the db and created a new one.

    Since, I do have a full file sytem backup of the relevant Oracle files
    (Control files, datafiles, archived redo log files), I was trying to see if
    I can recover any of the data in those datafiles into a new oracle db. My
    apologies, if I sound too simplistic... as I said, I am a rookie in this
    arena.

    Regards,
    Srinivas.

    On Sat, Apr 26, 2008 at 1:37 PM, Hemant K Chitale
    wrote:
    "Just take a backup, ...."
    Even in Oracle it is very easy . You need to know which files to backup.
    However, I believe that you had posted this on forums.oracle.com as well.
    You had a datafile where the filesize in the header doesn't match the size
    on
    the filesystem -- possibly an instance abort / server failure while the
    file was
    being resized.
    At 09:31 PM Friday, Srinivas Chintamani wrote:

    Hi,
    The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem. What I am trying to do is to
    find out if, it is possible to load the data from the datafiles of the
    crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup / restore
    the db. Just take a backup, dump the backup file anywhere on the filesystem
    and point to SQL Server, where the backup file is at and it happily
    recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.
    Hemant K Chitale
    http://hemantoracledba.blogspot.com

    "A 'No' uttered from the deepest conviction is better than a 'Yes' merely
    uttered to please, or worse, to avoid trouble."
    Mohandas Gandhi Quotes :
    http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
    --
    Regards,
    Srinivas Chintamani

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Apr 28, 2008 at 9:40 pm

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani < srinivas.chintamani_at_gmail.com> wrote:


    Earlier when working with SQL Server, it was simple to backup / restore
    the db. Just take a backup, dump the backup file anywhere on the filesystem
    and point to SQL Server, where the backup file is at and it happily
    recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...
    Comparing a tool you know to one you don't know is not really a fair
    comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and Oracle.
    Backing up an oracle database is more comparable to backing up an entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the log
    fill
    the disk. Try reopening the database following that. It's a lot of fun.

    No database is perfect. If you are responsible for backups of a database,
    the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can help
    you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt, correct?

    IIRC there may be a way to open this db, though some corruption may occur.

    Have you escalated the SR? This is not the same as setting a severity
    level.

    Search for 'escalation' on MetaLink.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Josh Collier at Apr 28, 2008 at 11:11 pm
    Restore a good copy of the datafile from a backup and perform recovery

    as needed.

    still no mention of

    1. your db version
    2. your os
    3. the contents of your alert log when the system crashed.
    4. your pfile contents
    5. your control file statement.

    your recovery would have been simple if you had a tested recovery strategy. Of the two words in the phrase "backup and recovery"; recovery is the more important.

    Where you trying to resize the undo tablespace and hit control-c?

    you could try putting the db in manual undo and starting it with the default rollback seg in the system tablespace.

    From: oracle-l-bounce_at_freelists.org On Behalf Of Jared Still
    Sent: Monday, April 28, 2008 2:40 PM
    To: srinivas.chintamani_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Is it possible to add existing datafiles to an oracle database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani > wrote:

    Earlier when working with SQL Server, it was simple to backup / restore the db. Just take a backup, dump the backup file anywhere on the filesystem and point to SQL Server, where the backup file is at and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Comparing a tool you know to one you don't know is not really a fair comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and Oracle.
    Backing up an oracle database is more comparable to backing up an entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the log fill
    the disk. Try reopening the database following that. It's a lot of fun.

    No database is perfect. If you are responsible for backups of a database, the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can help you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt, correct?

    IIRC there may be a way to open this db, though some corruption may occur.

    Have you escalated the SR? This is not the same as setting a severity level.

    Search for 'escalation' on MetaLink.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Apr 25, 2008 at 1:34 pm
    Oracle has a very expensive tool that will do this for you. I also
    believe there is a third party tool out there somewhere that will do
    this too, don't know what that costs, but I doubt it's cheap.

    The question is, why wont your database come up? What kind of error are you getting? Are you sure it can not be recovered?

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Srinivas Chintamani
    To: oracle-l_at_freelists.org
    Sent: Friday, April 25, 2008 7:10:24 AM
    Subject: Is it possible to add existing datafiles to an oracle database?

    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all the datafiles for this database and was wondering if it is possible to somehow "Load / Import" the data in these datafiles into another working database. If yes, it will save me quite a lot of re-work. Can somebody help me on this please ....

    --
    Regards,
    Srinivas Chintamani
    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Apr 25, 2008 at 1:47 pm
    LOL.... restoring Oracle is not a pain if you have thought about it BEFORE the crash. Backing up Oracle is easy. However, you need the datafiles, the control file and the online redo logs (if in NOARCHIVELOG mode) backed up with the databse down. If you have those (or if in ARCHIVELOG mode if you have the archived redo logs and you backed up the datafiles in hot backup mode or you used RMAN) then you should be able to recover.

    Sounds like your problem is that you stepped up from McMicrosoft to a real database and didn't bother to understand what you were stepping up to. Don't blame the database for your lack of planning. If only I had a fiver for the number of times people assumed a particular backup strategy would work, and it didn't. It's all right there, in the documentation (or in books)... all one has to do is read and apply said knowledge. Oh... testing your backup strategy is also probably a good idea.

    Sheesh...

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Srinivas Chintamani
    To: David Pintor
    Cc: oracle-l_at_freelists.org
    Sent: Friday, April 25, 2008 7:31:14 AM
    Subject: Re: Is it possible to add existing datafiles to an oracle database?

    Hi,
    The crashed database is deleted now and I only have the datafiles of the crashed database somewhere on my filesystem. What I am trying to do is to find out if, it is possible to load the data from the datafiles of the crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup / restore the db. Just take a backup, dump the backup file anywhere on the filesystem and point to SQL Server, where the backup file is at and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.

    On Fri, Apr 25, 2008 at 6:50 PM, David Pintor wrote:

    Hi,

    I'm not really sure if there's a way of doing that, but before thinking about this I would try to recover the database first, or if your datafiles are in a consistent state, I would try to recreate the control file and try to restart...

    Good luck.

    David

    On 25/04/2008, Srinivas Chintamani wrote:
    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all the datafiles for this database and was wondering if it is possible to somehow "Load / Import" the data in these datafiles into another working database. If yes, it will save me quite a lot of re-work. Can somebody help me on this please ....

    --
    Regards,
    Srinivas Chintamani

    --
    Regards,
    Srinivas Chintamani
    --
    http://www.freelists.org/webpage/oracle-l
  • Srinivas Chintamani at Apr 28, 2008 at 3:47 am
    Hi Robert,
    I agree... I didn't really know what I was into before this db crashed and I
    had the devs breathing down my neck to get their db up and running. Now
    that I know its not a simple thing to recover a crashed db, would you have a
    check-list that I might use to prevent this kind of mishap from happening
    again?

    Regards,
    Srinivas.

    On Fri, Apr 25, 2008 at 7:17 PM, Robert Freeman
    wrote:
    LOL.... restoring Oracle is not a pain if you have thought about it BEFORE
    the crash. Backing up Oracle is easy. However, you need the datafiles, the
    control file and the online redo logs (if in NOARCHIVELOG mode) backed up
    with the databse down. If you have those (or if in ARCHIVELOG mode if you
    have the archived redo logs and you backed up the datafiles in hot backup
    mode or you used RMAN) then you should be able to recover.

    Sounds like your problem is that you stepped up from McMicrosoft to a real
    database and didn't bother to understand what you were stepping up to. Don't
    blame the database for your lack of planning. If only I had a fiver for the
    number of times people assumed a particular backup strategy would work, and
    it didn't. It's all right there, in the documentation (or in books)... all
    one has to do is read and apply said knowledge. Oh... testing your backup
    strategy is also probably a good idea.

    Sheesh...

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    ----- Original Message ----
    From: Srinivas Chintamani
    To: David Pintor
    Cc: oracle-l_at_freelists.org
    Sent: Friday, April 25, 2008 7:31:14 AM
    Subject: Re: Is it possible to add existing datafiles to an oracle
    database?

    Hi,
    The crashed database is deleted now and I only have the datafiles of the
    crashed database somewhere on my filesystem. What I am trying to do is to
    find out if, it is possible to load the data from the datafiles of the
    crashed database into another new database.

    Earlier when working with SQL Server, it was simple to backup / restore
    the db. Just take a backup, dump the backup file anywhere on the filesystem
    and point to SQL Server, where the backup file is at and it happily
    recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Regards,
    Srinivas.

    On Fri, Apr 25, 2008 at 6:50 PM, David Pintor
    wrote:
    Hi,

    I'm not really sure if there's a way of doing that, but before thinking
    about this I would try to recover the database first, or if your datafiles
    are in a consistent state, I would try to recreate the control file and try
    to restart...

    Good luck.

    David


    On 25/04/2008, Srinivas Chintamani
    wrote:
    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have
    all the datafiles for this database and was wondering if it is possible to
    somehow "Load / Import" the data in these datafiles into another working
    database. If yes, it will save me quite a lot of re-work. Can somebody
    help me on this please ....

    --
    Regards,
    Srinivas Chintamani

    --
    Regards,
    Srinivas Chintamani
    --
    Regards,
    Srinivas Chintamani

    --
    http://www.freelists.org/webpage/oracle-l
  • Murtuja Khokhar at Apr 28, 2008 at 4:34 am
    Hi,



    I have two node RAC setup. I am using Oracle 10g Standard Edition
    (10.2.0.3.0) on Solaris 10 (sparc 64bit).

    I am testing node crash scenario so I have deleted one node. I have
    followed steps from ML#269320.1.After deleting node successfully I have
    tried to add node. For adding node I have followed ML#270512.1



    When I started to add new node, I was able to add clusterware to new
    node. VIPCA was successful.

    but when I tried to add Oracle Software on new node,I am getting error
    message like "The selected remote nodes are not accessible" and "The
    virtual host name for node is already in use"



    For troubleshoot this error :



    I have checked user equivalence (SSH) is OK and All nodes can ping each
    other.

    vip is configured properly and both are on eri0:1 and I can ping both
    vip from each node.



    Can you please suggest me fix for this error?
  • Krish.hariharan_at_quasardb.com at Apr 28, 2008 at 5:36 am
    A long shot - verify that X11 forwarding is no in the ssh_config files of
    both nodes. I have had problems with that before where internode
    connectivity is affected (I believe that there is a ML note on that). I
    would suspect that the second message is as a consequence of the first, but
    regardless, resolving the first error would verify that.



    -Krish



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Murtuja Khokhar
    Sent: Sunday, April 27, 2008 10:34 PM
    To: oracle-l_at_freelists.org
    Subject: Error while adding new node



    Hi,



    I have two node RAC setup. I am using Oracle 10g Standard Edition
    (10.2.0.3.0) on Solaris 10 (sparc 64bit).

    I am testing node crash scenario so I have deleted one node. I have followed
    steps from ML#269320.1.After deleting node successfully I have tried to add
    node. For adding node I have followed ML#270512.1



    When I started to add new node, I was able to add clusterware to new node.
    VIPCA was successful.

    but when I tried to add Oracle Software on new node,I am getting error
    message like "The selected remote nodes are not accessible" and "The
    virtual host name for node is already in use"



    For troubleshoot this error :



    I have checked user equivalence (SSH) is OK and All nodes can ping each
    other.

    vip is configured properly and both are on eri0:1 and I can ping both vip
    from each node.



    Can you please suggest me fix for this error?
  • Murtuja Khokhar at Apr 28, 2008 at 6:28 am
    Hi Krish,



    I have checked from /etc/ssh/ssh_config and /etc/ssh/ssh_config. X11
    forwarding is set to no.



    Thanks for reply



    Regards,

    Murtuja Khokhar

    Oracle Database Administrator

    Indorion Networks Pvt. Ltd.

    Lower Level, Tower A (Mateen Tower)

    Diamond District

    150 Airport Road, Bangalore 560008 India

    Email:mkhokhar_at_indorion.com

    Office: +91 80 414 72000

    Extension: 1115

    Mobile: +91 98865 57151

    Fax: +91 80 414 72050



    From: krish.hariharan_at_quasardb.com

    Sent: Monday, April 28, 2008 11:07 AM
    To: Murtuja Khokhar; oracle-l_at_freelists.org
    Subject: RE: Error while adding new node



    A long shot - verify that X11 forwarding is no in the ssh_config files
    of both nodes. I have had problems with that before where internode
    connectivity is affected (I believe that there is a ML note on that). I
    would suspect that the second message is as a consequence of the first,
    but regardless, resolving the first error would verify that.



    -Krish



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Murtuja Khokhar
    Sent: Sunday, April 27, 2008 10:34 PM
    To: oracle-l_at_freelists.org
    Subject: Error while adding new node



    Hi,



    I have two node RAC setup. I am using Oracle 10g Standard Edition
    (10.2.0.3.0) on Solaris 10 (sparc 64bit).

    I am testing node crash scenario so I have deleted one node. I have
    followed steps from ML#269320.1.After deleting node successfully I have
    tried to add node. For adding node I have followed ML#270512.1



    When I started to add new node, I was able to add clusterware to new
    node. VIPCA was successful.

    but when I tried to add Oracle Software on new node,I am getting error
    message like "The selected remote nodes are not accessible" and "The
    virtual host name for node is already in use"



    For troubleshoot this error :



    I have checked user equivalence (SSH) is OK and All nodes can ping each
    other.

    vip is configured properly and both are on eri0:1 and I can ping both
    vip from each node.



    Can you please suggest me fix for this error?
  • Finn Jorgensen at Apr 29, 2008 at 12:13 am
    Murtuja,

    As crazy as this sounds, I've had a similar issues 2-3 years ago and the
    problem was the ssh keys were installed to allow for non-password logins.
    When I removed that configuration (authorized_keys) the add node worked.
    After the node was added I could put the ssh config back in place. I know it
    sounds crazy. This was on 10.2.0.1 on Solaris.

    Finn

    On Mon, Apr 28, 2008 at 1:28 AM, Murtuja Khokhar
    wrote:
    Hi Krish,



    I have checked from /etc/ssh/ssh_config and /etc/ssh/ssh_config. X11
    forwarding is set to *no*.



    Thanks for reply



    Regards,

    Murtuja Khokhar

    Oracle Database Administrator

    *Ind**orion Networks Pvt. Ltd.***

    Lower Level, Tower A (Mateen Tower)

    Diamond District

    150 Airport Road, Bangalore 560008 India

    Email:mkhokhar_at_indorion.com

    Office: +91 80 414 72000

    Extension: 1115

    Mobile: +91 98865 57151

    Fax: +91 80 414 72050



    *From:* krish.hariharan_at_quasardb.com

    *Sent:* Monday, April 28, 2008 11:07 AM
    *To:* Murtuja Khokhar; oracle-l_at_freelists.org
    *Subject:* RE: Error while adding new node



    A long shot � verify that X11 forwarding is no in the ssh_config files of
    both nodes. I have had problems with that before where internode
    connectivity is affected (I believe that there is a ML note on that). I
    would suspect that the second message is as a consequence of the first, but
    regardless, resolving the first error would verify that.



    -Krish


    ------------------------------

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Murtuja Khokhar
    *Sent:* Sunday, April 27, 2008 10:34 PM
    *To:* oracle-l_at_freelists.org
    *Subject:* Error while adding new node



    Hi,



    I have two node RAC setup. I am using Oracle 10g Standard Edition
    (10.2.0.3.0) on Solaris 10 (sparc 64bit).

    I am testing node crash scenario so I have deleted one node. I have
    followed steps from ML#269320.1.After deleting node successfully I have
    tried to add node. For adding node I have followed ML#270512.1



    When I started to add new node, I was able to add clusterware to new node.
    VIPCA was successful.

    but when I tried to add Oracle Software on new node,I am getting error
    message like "The selected remote nodes are not accessible" and "The
    virtual host name for node is already in use"



    For troubleshoot this error :



    I have checked user equivalence (SSH) is OK and All nodes can ping each
    other.

    vip is configured properly and both are on eri0:1 and I can ping both vip
    from each node.



    Can you please suggest me fix for this error?

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Apr 28, 2008 at 11:11 am
    In your immediately previous post, you noted that the startup problem was
    that the UNDO tablespace had a datafile size check fail. Still no mention of
    the cause of the crash. Do you have alert logs? They might tell you
    something. Did someone unplug the disk array? Did someone start an OS level
    compress on UNDOTBS01.DBF?



    There are two cases that I can think of, though for the file to be the wrong
    size on the restart attempt:



    Someone put a different file in place or somehow truncated the file
    at the operating system level.

    Autoextend was on for the UNDO tablespace's file and the dictionary
    was updated before the completion of the file system write(s) actually
    increasing the size of the file.

    For 1), you just get a full size version of the file from a backup and
    recover it.

    For 2), which I didn't think could happen (though a virtual file system or a
    sparse file system could possibly be set up to allow the OS to lie to Oracle
    that the extension was complete when in fact it was deferred), you might
    possibly have to recover both SYSTEM and UNDO from an older file set, and
    this time have enough real space for the extension to complete.



    As for a checklist, there are n (where n is a pretty big number including
    Robert's own book and the Oracle manuals) sources of the various backup and
    recovery processes for Oracle databases. Knowing what caused the crash in
    the first place is usually a good starting point for recovery, right after
    preserving an on line copy of the online redo logs. If you're using RMAN,
    that pretty much keeps track of what to do next. If you're managing your own
    physical backups, then you need to be sure you have complete file images of
    all the tablespace files, all the interceding archived redo logs since the
    oldest of the file images, and a reasonable control file image (either
    current or backup). Usually instance recovery from the existing images on
    line completes automagically just by starting the database. If a piece of
    the database is missing, Oracle tells you what is wrong. In your case it
    told you that a file of the UNDO tablespace was not all there. So in your
    specific case the checklist was: "Find out what happened to the rest of my
    file. Fix that. Recover."



    You keep repeating that it is not a simple thing to recover a crashed db.
    That is not true. I wonder why you keep repeating that falsehood.



    It is true that it may be impossible to recover anything if arbitrary
    actions are taken that are incompatible with recovery. Try recovering sql
    server if half the file has been removed.



    Now you have written:



    "Since, I do have a full file sytem backup of the relevant Oracle files
    (Control files, datafiles, archived redo log files), I was trying to see if
    I can recover any of the data in those datafiles into a new oracle db. My
    apologies, if I sound too simplistic... as I said, I am a rookie in this
    arena."



    So it may indeed still be possible to get your whole database back.
    Apparently you may have overwritten your online redologs, so you might not
    be able to get all the way to the present (the unarchived changes will be
    lost, and transacations in progress at that point will be rolled back.) I am
    a bit troubled by your use of the word "relevant" but if you really have all
    the files you should be able to recover. You might need an older vintage of
    the file data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF', since that
    file is damaged as of the startup attempts prior to your ill advised notion
    to create a new database of the same name. (By the way if the "full file
    system backup of the relevant Oracle files" is from after you did this, then
    you don't have a backup of your old database unless you have an older set of
    files and the interceding archived redo logs.).



    I really do wish you good luck, and I think it is possible you may still
    recover, but please stop insisting that this is not simple. That just makes
    me think you work for Microsoft and this is a complete dodge to paint
    Oracle's incredibly good recovery model as something less than it is.
    Creating the best possible recovery strategy to handle site disasters and
    business continuation is a complex undertaking because of the highly
    variable needs of businesses and the cost benefit tradeoff analysis of
    building the infrastructure to handle something like that, but the simple
    backup and recovery of a single local Oracle database is just that: simple.



    Regards,



    mwf



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Srinivas Chintamani
    Sent: Sunday, April 27, 2008 11:48 PM
    To: Robert Freeman
    Cc: David Pintor; oracle-l_at_freelists.org
    Subject: Re: Is it possible to add existing datafiles to an oracle database?



    Hi Robert,
    I agree... I didn't really know what I was into before this db crashed and I
    had the devs breathing down my neck to get their db up and running. Now
    that I know its not a simple thing to recover a crashed db, would you have a
    check-list that I might use to prevent this kind of mishap from happening
    again?

    Regards,
    Srinivas.
  • Jack van Zanen at Apr 28, 2008 at 10:08 pm
    Haven't tried this before, but....

    Can't you change the init file to undo management manual and recreate the
    controlfile without that tablespace.
    There may be some rollback that you need for crash recovery in which case
    you're stuffed
    On 28/04/2008, Mark W. Farnham wrote:

    In your immediately previous post, you noted that the startup problem was
    that the UNDO tablespace had a datafile size check fail. Still no mention of
    the cause of the crash. Do you have alert logs? They might tell you
    something. Did someone unplug the disk array? Did someone start an OS level
    compress on UNDOTBS01.DBF?



    There are two cases that I can think of, though for the file to be the
    wrong size on the restart attempt:



    1) Someone put a different file in place or somehow truncated the
    file at the operating system level.

    2) Autoextend was on for the UNDO tablespace's file and the
    dictionary was updated before the completion of the file system write(s)
    actually increasing the size of the file.



    For 1), you just get a full size version of the file from a backup and
    recover it.

    For 2), which I didn't think could happen (though a virtual file system or
    a sparse file system could possibly be set up to allow the OS to lie to
    Oracle that the extension was complete when in fact it was deferred), you
    might possibly have to recover both SYSTEM and UNDO from an older file set,
    and this time have enough real space for the extension to complete.



    As for a checklist, there are n (where n is a pretty big number including
    Robert's own book and the Oracle manuals) sources of the various backup and
    recovery processes for Oracle databases. Knowing what caused the crash in
    the first place is usually a good starting point for recovery, right after
    preserving an on line copy of the online redo logs. If you're using RMAN,
    that pretty much keeps track of what to do next. If you're managing your own
    physical backups, then you need to be sure you have complete file images of
    all the tablespace files, all the interceding archived redo logs since the
    oldest of the file images, and a reasonable control file image (either
    current or backup). Usually instance recovery from the existing images on
    line completes automagically just by starting the database. If a piece of
    the database is missing, Oracle tells you what is wrong. In your case it
    told you that a file of the UNDO tablespace was not all there. So in your
    specific case the checklist was: "Find out what happened to the rest of my
    file. Fix that. Recover."



    You keep repeating that it is not a simple thing to recover a crashed db.
    That is not true. I wonder why you keep repeating that falsehood.



    It is true that it may be impossible to recover anything if arbitrary
    actions are taken that are incompatible with recovery. Try recovering sql
    server if half the file has been removed.



    Now you have written:



    "Since, I do have a full file sytem backup of the relevant Oracle files
    (Control files, datafiles, archived redo log files), I was trying to see if
    I can recover any of the data in those datafiles into a new oracle db. My
    apologies, if I sound too simplistic... as I said, I am a rookie in this
    arena."



    So it may indeed still be possible to get your whole database back.
    Apparently you may have overwritten your online redologs, so you might not
    be able to get all the way to the present (the unarchived changes will be
    lost, and transacations in progress at that point will be rolled back.) I am
    a bit troubled by your use of the word "relevant" but if you really have all
    the files you should be able to recover. You might need an older vintage of
    the file data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF', since that
    file is damaged as of the startup attempts prior to your ill advised notion
    to create a new database of the same name. (By the way if the "full file
    system backup of the relevant Oracle files" is from after you did this, then
    you don't have a backup of your old database unless you have an older set of
    files and the interceding archived redo logs.).



    I really do wish you good luck, and I think it is possible you may still
    recover, but please stop insisting that this is not simple. That just makes
    me think you work for Microsoft and this is a complete dodge to paint
    Oracle's incredibly good recovery model as something less than it is.
    Creating the best possible recovery strategy to handle site disasters and
    business continuation is a complex undertaking because of the highly
    variable needs of businesses and the cost benefit tradeoff analysis of
    building the infrastructure to handle something like that, but the simple
    backup and recovery of a single local Oracle database is just that: simple.



    Regards,



    mwf


    ------------------------------

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Srinivas Chintamani
    *Sent:* Sunday, April 27, 2008 11:48 PM
    *To:* Robert Freeman
    *Cc:* David Pintor; oracle-l_at_freelists.org
    *Subject:* Re: Is it possible to add existing datafiles to an oracle
    database?



    Hi Robert,
    I agree... I didn't really know what I was into before this db crashed and
    I had the devs breathing down my neck to get their db up and running. Now
    that I know its not a simple thing to recover a crashed db, would you have a
    check-list that I might use to prevent this kind of mishap from happening
    again?

    Regards,
    Srinivas.

    --
    J.A. van Zanen

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Apr 28, 2008 at 11:26 pm
    I offered the OP some information on trying to get the DB open with UNDO corruption using hidden/undocumented parameters. Since it's not supported and quite risky and easy to find if you google .... I opted not to post it here.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Jared Still
    To: srinivas.chintamani_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Sent: Monday, April 28, 2008 3:40:14 PM
    Subject: Re: Is it possible to add existing datafiles to an oracle database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani wrote:

    Earlier when working with SQL Server, it was simple to backup / restore the db. Just take a backup, dump the backup file anywhere on the filesystem and point to SQL Server, where the backup file is at and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Comparing a tool you know to one you don't know is not really a fair comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and Oracle.
    Backing up an oracle database is more comparable to backing up an entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the log fill
    the disk. Try reopening the database following that. It's a lot of fun.

    No database is perfect. If you are responsible for backups of a database, the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can help you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt, correct?

    IIRC there may be a way to open this db, though some corruption may occur.

    Have you escalated the SR? This is not the same as setting a severity level.

    Search for 'escalation' on MetaLink.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Apr 28, 2008 at 11:29 pm
    The OP does not seem to have a valid backup.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: Josh Collier
    To: "srinivas.chintamani_at_gmail.com"
    Cc: "oracle-l_at_freelists.org"
    Sent: Monday, April 28, 2008 5:11:32 PM
    Subject: RE: Is it possible to add existing datafiles to an oracle database?

    Restore a good copy of the datafile
    from a backup and perform recovery

    as needed.




    still no mention of
    1.
    your db version
    2.
    your os
    3. the
    contents of your alert log when the system crashed.
    4.
    your pfile contents
    5.
    your control file statement.


    your recovery would have been simple if you had a tested
    recovery strategy. Of the two words in the phrase "backup and recovery";
    recovery is the more important.


    Where
    you trying to resize the undo tablespace and hit control-c?


    you
    could try putting the db in manual undo and starting it with the default
    rollback seg in the system tablespace.


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared
    Still
    Sent: Monday, April 28, 2008 2:40 PM
    To: srinivas.chintamani_at_gmail.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Is it possible to add existing
    datafiles to an oracle database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani
    wrote:

    Earlier when working with SQL Server, it was simple to backup / restore the db. Just take a backup, dump the backup file anywhere on the filesystem and point to SQL Server, where the backup file is at and it happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...

    Comparing a tool you know to one you don't know is not really a fair
    comparison.

    I'm somewhat familiar with backing up and restoring SQL
    Server and Oracle.
    Backing up an oracle database is more comparable to
    backing up an entire
    SQL Server instance, not a single SQL Server
    database.

    Try this on SQL Server: set the log file to unlimited
    growth. Let the log fill
    the disk. Try reopening the database
    following that. It's a lot of fun.

    No database is perfect. If you
    are responsible for backups of a database, the first
    thing you should make
    sure you can do is restore said backups.

    Now that you've (hopefully)
    learned that lesson, perhaps someone can help you
    with your down
    database.

    Further down in the thread it appears that the UNDO
    tbs is corrupt, correct?

    IIRC there may be a way to open this db, though
    some corruption may occur.

    Have you escalated the SR? This is not
    the same as setting a severity level.

    Search for 'escalation' on
    MetaLink.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time
    Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Srinivas Chintamani at Apr 29, 2008 at 4:32 am
    Hi All,
    I am extremely delighted at the number of responses I got to my question. I
    have tried most everything suggested, including setting the
    _Allow_LOG_Corruption parameter to true. It pains me to report that, it
    didn't solve my problem. Anyways, I guess its a lesson - learnt the hard
    way; Not having a tested backup. I guess I will be more careful in future.

    Thank you all for your helpful comments / suggestions.

    Regards,
    Srinivas.

    On Tue, Apr 29, 2008 at 4:59 AM, Robert Freeman
    wrote:
    The OP does not seem to have a valid backup.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)


    ----- Original Message ----
    From: Josh Collier
    To: "srinivas.chintamani_at_gmail.com"
    Cc: "oracle-l_at_freelists.org"
    Sent: Monday, April 28, 2008 5:11:32 PM
    Subject: RE: Is it possible to add existing datafiles to an oracle
    database?

    Restore a good copy of the datafile from a backup and perform recovery
    as needed.



    still no mention of
    1. your db version
    2. your os
    3. the contents of your alert log when the system crashed.
    4. your pfile contents
    5. your control file statement.

    your recovery would have been simple if you had a tested recovery
    strategy. Of the two words in the phrase "backup and recovery"; recovery is
    the more important.

    Where you trying to resize the undo tablespace and hit control-c?

    you could try putting the db in manual undo and starting it with the
    default rollback seg in the system tablespace.



    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Jared Still
    *Sent:* Monday, April 28, 2008 2:40 PM
    *To:* srinivas.chintamani_at_gmail.com
    *Cc:* oracle-l_at_freelists.org
    *Subject:* Re: Is it possible to add existing datafiles to an oracle
    database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani <
    srinivas.chintamani_at_gmail.com> wrote:
    Earlier when working with SQL Server, it was simple to backup / restore
    the db. Just take a backup, dump the backup file anywhere on the filesystem
    and point to SQL Server, where the backup file is at and it happily
    recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...
    Comparing a tool you know to one you don't know is not really a fair
    comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and Oracle.
    Backing up an oracle database is more comparable to backing up an entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the log
    fill
    the disk. Try reopening the database following that. It's a lot of fun.

    No database is perfect. If you are responsible for backups of a database,
    the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can help
    you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt,
    correct?

    IIRC there may be a way to open this db, though some corruption may occur.

    Have you escalated the SR? This is not the same as setting a severity
    level.

    Search for 'escalation' on MetaLink.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Regards,
    Srinivas Chintamani

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 1, 2008 at 5:44 pm
    There's more to using the _Allow_LOG_Corruption parameter than just setting
    it to true.

    You really need to work with Oracle Support to get it to work.

    On Mon, Apr 28, 2008 at 9:32 PM, Srinivas Chintamani <
    srinivas.chintamani_at_gmail.com> wrote:
    Hi All,
    I am extremely delighted at the number of responses I got to my question.
    I have tried most everything suggested, including setting the
    _Allow_LOG_Corruption parameter to true. It pains me to report that, it
    didn't solve my problem. Anyways, I guess its a lesson - learnt the hard
    way; Not having a tested backup. I guess I will be more careful in future.

    Thank you all for your helpful comments / suggestions.

    Regards,
    Srinivas.


    On Tue, Apr 29, 2008 at 4:59 AM, Robert Freeman
    wrote:
    The OP does not seem to have a valid backup.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)


    ----- Original Message ----
    From: Josh Collier
    To: "srinivas.chintamani_at_gmail.com"
    Cc: "oracle-l_at_freelists.org"
    Sent: Monday, April 28, 2008 5:11:32 PM
    Subject: RE: Is it possible to add existing datafiles to an oracle
    database?

    Restore a good copy of the datafile from a backup and perform recovery
    as needed.



    still no mention of
    1. your db version
    2. your os
    3. the contents of your alert log when the system crashed.
    4. your pfile contents
    5. your control file statement.

    your recovery would have been simple if you had a tested recovery
    strategy. Of the two words in the phrase "backup and recovery"; recovery is
    the more important.

    Where you trying to resize the undo tablespace and hit control-c?

    you could try putting the db in manual undo and starting it with the
    default rollback seg in the system tablespace.



    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Jared Still
    *Sent:* Monday, April 28, 2008 2:40 PM
    *To:* srinivas.chintamani_at_gmail.com
    *Cc:* oracle-l_at_freelists.org
    *Subject:* Re: Is it possible to add existing datafiles to an oracle
    database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani <
    srinivas.chintamani_at_gmail.com> wrote:
    Earlier when working with SQL Server, it was simple to backup /
    restore the db. Just take a backup, dump the backup file anywhere on the
    filesystem and point to SQL Server, where the backup file is at and it
    happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...
    Comparing a tool you know to one you don't know is not really a fair
    comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and
    Oracle.
    Backing up an oracle database is more comparable to backing up an entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the
    log fill
    the disk. Try reopening the database following that. It's a lot of fun.

    No database is perfect. If you are responsible for backups of a
    database, the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can
    help you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt,
    correct?

    IIRC there may be a way to open this db, though some corruption may
    occur.

    Have you escalated the SR? This is not the same as setting a severity
    level.

    Search for 'escalation' on MetaLink.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist


    --
    Regards,
    Srinivas Chintamani
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Stefan Knecht at May 4, 2008 at 9:49 am
    Just for completeness' sake -- as it has been mentioned more than once in
    this thread (and wrong every time) :)

    The parameter in question is probably _allow_resetlogs_corruption... there
    is no _allow_log_corruption parameter in oracle.

    Cheers

    Stefan
    On Thu, May 1, 2008 at 7:44 PM, Jared Still wrote:

    There's more to using the _Allow_LOG_Corruption parameter than just
    setting it to true.

    You really need to work with Oracle Support to get it to work.



    On Mon, Apr 28, 2008 at 9:32 PM, Srinivas Chintamani <
    srinivas.chintamani_at_gmail.com> wrote:
    Hi All,
    I am extremely delighted at the number of responses I got to my
    question. I have tried most everything suggested, including setting the
    _Allow_LOG_Corruption parameter to true. It pains me to report that, it
    didn't solve my problem. Anyways, I guess its a lesson - learnt the hard
    way; Not having a tested backup. I guess I will be more careful in future.

    Thank you all for your helpful comments / suggestions.

    Regards,
    Srinivas.


    On Tue, Apr 29, 2008 at 4:59 AM, Robert Freeman <
    robertgfreeman_at_yahoo.com> wrote:
    The OP does not seem to have a valid backup.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)


    ----- Original Message ----
    From: Josh Collier
    To: "srinivas.chintamani_at_gmail.com"
    Cc: "oracle-l_at_freelists.org"
    Sent: Monday, April 28, 2008 5:11:32 PM
    Subject: RE: Is it possible to add existing datafiles to an oracle
    database?

    Restore a good copy of the datafile from a backup and perform recovery

    as needed.



    still no mention of
    1. your db version
    2. your os
    3. the contents of your alert log when the system crashed.
    4. your pfile contents
    5. your control file statement.

    your recovery would have been simple if you had a tested recovery
    strategy. Of the two words in the phrase "backup and recovery"; recovery is
    the more important.

    Where you trying to resize the undo tablespace and hit control-c?

    you could try putting the db in manual undo and starting it with the
    default rollback seg in the system tablespace.



    ------------------------------
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Jared Still
    *Sent:* Monday, April 28, 2008 2:40 PM
    *To:* srinivas.chintamani_at_gmail.com
    *Cc:* oracle-l_at_freelists.org
    *Subject:* Re: Is it possible to add existing datafiles to an oracle
    database?

    On Fri, Apr 25, 2008 at 6:31 AM, Srinivas Chintamani <
    srinivas.chintamani_at_gmail.com> wrote:
    Earlier when working with SQL Server, it was simple to backup /
    restore the db. Just take a backup, dump the backup file anywhere on the
    filesystem and point to SQL Server, where the backup file is at and it
    happily recovered the db, all in a few seconds.

    Wonder why restoring an oracle db is such a pain ...
    Comparing a tool you know to one you don't know is not really a fair
    comparison.

    I'm somewhat familiar with backing up and restoring SQL Server and
    Oracle.
    Backing up an oracle database is more comparable to backing up an
    entire
    SQL Server instance, not a single SQL Server database.

    Try this on SQL Server: set the log file to unlimited growth. Let the
    log fill
    the disk. Try reopening the database following that. It's a lot of
    fun.

    No database is perfect. If you are responsible for backups of a
    database, the first
    thing you should make sure you can do is restore said backups.

    Now that you've (hopefully) learned that lesson, perhaps someone can
    help you
    with your down database.

    Further down in the thread it appears that the UNDO tbs is corrupt,
    correct?

    IIRC there may be a way to open this db, though some corruption may
    occur.

    Have you escalated the SR? This is not the same as setting a severity
    level.

    Search for 'escalation' on MetaLink.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist


    --
    Regards,
    Srinivas Chintamani



    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    =========================

    Stefan P Knecht
    Senior Consultant
    Infrastructure Managed Services

    Trivadis AG
    Europa-Strasse 5
    CH-8152 Glattbrugg

    Phone +41-44-808 70 20
    Fax +41-808 70 12
    Mobile +41-79-571 36 27
    stefan.knecht_at_trivadis.com
    http://www.trivadis.com

    OCP 9i/10g SCSA SCNA
    =========================

    --
    http://www.freelists.org/webpage/oracle-l
  • Peter Teoh at May 2, 2008 at 5:49 am

    On 4/25/08, Srinivas Chintamani wrote:
    Hi,
    I ended up with a crashed Oracle db, which refuses to start. I have all the
    datafiles for this database and was wondering if it is possible to somehow
    "Load / Import" the data in these datafiles into another working database.
    If yes, it will save me quite a lot of re-work. Can somebody help me on
    this please ....

    --
    Regards,
    Srinivas Chintamani
    As suggested by someone else on this list, Oracle has a tool called
    DUL, to extract the data from the datafiles without Oracle starting
    up. Alternatively, there are 3rd party tools like:

    http://www.ora600.nl/pricing.htm

    http://www.anysql.net/en

    Or a few others as listed here:

    http://www.anysql.net/tools/eight_dul_like_tools.html:

    Bernard's Data UnLoader
    DUDE/jDUL
    AnySQL UnLoader (AUL)
    Oracle Salvage
    OracleRecovery
    Recovery for Oracle
    MyDUL
    CLOUT

    Sorry, I know nothing about them. Does anyone know any opensource
    tools that can do this?

Related Discussions

People

Translate

site design / logo © 2022 Grokbase