FAQ
I use the term "interesting" in the subject line loosely!

I'm cloning an Oracle 9i (9208) production database to a different test
server.
I decided to use an existing rman backup of the database and some
archivelogs. The rman backup was taken in October to clone the
production database to a reporting database on the same server. We don't
use rman normally for backups. (We probably should though!)

After 36 hours restoring the database files, the recovery stage failed
as there were missing archivelogs between the highest available in the
rman backup and the lowest available online at the moment.

As I'm not really interested in having the clone right up to date, I'm
happy to do a Point In Time recovery of the clone. And this is where I
lose the plot!

Any command I use such as:

recover clone database to scn ???????;
recover clone database to time "date & time";
recover clone database to sequence ????? thread 1;

All fail with a message that my SYSTEM data file will be inconsistent if
I open the database with resetlogs.

I have tried PITR (within RMAN) to :

The date and time that the backup (of the database file, not the
archivelogs) completed.
The SCN in the first, middle and last archivelog files that are
present in the rman backup of the logs.
The log sequence of the first, second, third, middle few, last half
dozen log files that are part of the backup.

Robert Freeman's book "Oracle 9i RMAN Backup & Recovery" has "Case Study
#9 Completing a failed duplication manually" on pages 467 and 468, which
is right up my street - unfortunately, Tim - the example DBA - is in
full possession of ALL the archived logs. Lucky Tim, I have gaps!

My first question is, do I have any chance of getting a recovery done
that will get me past this error or should I just give it a miss and
start another duplication with a fresh rman backup?

My second question is, rman appears to recover clone database then
delete archivelogs. How do I managed to restore the deleted logs back to
the correct location on the clone server?

I'm not bothered about this database, I'm about to kick off a new clone
of the production one - that takes about 6 hours to complete. I'd love
to avoid another 36 odd hours of restoring/recovering if possible, but
if I have to do it the hard way, I'm ok with that.

If this was a production database problem, I would (probably) be
"bricking it" by now in this situation - if I had a gap in my archived
logs for some reason and a seeming inability to open the database in a
consistent manner. I'd like to know if there is a solution - just in
case!

Thanks,
Norman.

Norman Dunbar
Contract Oracle DBA
CIS Engineering Services
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Search Discussions

  • Niall Litchfield at Jan 27, 2010 at 10:52 am

    On Wed, Jan 27, 2010 at 10:40 AM, Dunbar, Norman < norman.dunbar_at_environment-agency.gov.uk> wrote:

    I use the term "interesting" in the subject line loosely!
    A courageous decision Minister! er anyway now I've got that out of my system
    for today.

    You won't be able to recover the db beyond the first missing archive log.
    You should however be able to recover up to that point. I wouldn't be using
    the RMAN clone feature in this circumstance, but do an incomplete recovery
    using RMAN and then use good ole NID to change the dbname and id.
  • Dunbar, Norman at Jan 27, 2010 at 11:06 am
    Morning Niall,
    You won't be able to recover the db beyond the first missing
    archive log. You should however be able to recover up to
    that point.
    Hmmm. That's the problem. I can restore and recover up to that point but
    I'm warned about inconsistencies if/when I open reset logs. I haven't
    found a position in the log sequence that gives me a consistent
    database. Most "interesting" indeed.

    I know the clone has been successfully used already as I have cloned the
    production database to the reporting database already (a while back)
    using it - not a problem there.
    I wouldn't be using the RMAN clone feature in
    this circumstance, but do an incomplete recovery using RMAN
    and then use good ole NID to change the dbname and id.
    NID etc is the solution in Robert's case study. I'm quite happy to do
    that - I just need a usable database first!

    Ah well, I'll keep looking/trying!

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Martin Bach at Jan 27, 2010 at 11:56 am
    Hi Norman,

    I like MOS note "How to recover and open the database if the archivelog
    required for recovery is either missing, lost or corrupted? [ID
    465478.1]" which might be of help.

    Regards,

    Martin
  • Dunbar, Norman at Jan 27, 2010 at 12:29 pm
    Morning Martin,
    I like MOS note "How to recover and open the database if the
    archivelog required for recovery is either missing, lost or
    corrupted? [ID
    465478.1]" which might be of help.
    Thanks. Yes, I had seen that one when researching this problem.
    Unfortunately, I get stumped by this bit in Option 2:

    "However, if we restored from an online or hot backup, then we may not
    be able to open the database, because we still need to apply the
    archivelogs generated during the said backup in order to synchronize the
    SCN of the datafiles before we can normally open the database."

    All my datafile headers are synchronised - which makes like
    "interesting".

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Taylor, Chris David at Jan 27, 2010 at 1:11 pm
    I know there's a hidden parameter to force open a database but I'm not sure if it has the same effect on an RMAN recovery as it does on a non-RMAN recovery. I assume it would.

    The parameter is:

    NAME VALUE DEFLT TYPE DESCRIPTION
    _allow_resetlogs_corruption FALSE TRUE boolean allow resetlogs even if it will cause corruption

    There is also this one that might be of interest
    _allow_read_only_corruption FALSE TRUE boolean allow read-only open even if database is corrupt

    These are ONLY as a last resort to allow you access to your data ...

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com


    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Dunbar, Norman
    Sent: Wednesday, January 27, 2010 6:30 AM
    To: Martin Bach
    Cc: Niall Litchfield; ORACLE-L
    Subject: RE: Interesting RMAN duplicate problem.

    Morning Martin,
    I like MOS note "How to recover and open the database if the
    archivelog required for recovery is either missing, lost or
    corrupted? [ID
    465478.1]" which might be of help.
    Thanks. Yes, I had seen that one when researching this problem.
    Unfortunately, I get stumped by this bit in Option 2:

    "However, if we restored from an online or hot backup, then we may not
    be able to open the database, because we still need to apply the
    archivelogs generated during the said backup in order to synchronize the
    SCN of the datafiles before we can normally open the database."

    All my datafile headers are synchronised - which makes like
    "interesting".

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Peter Hitchman at Jan 27, 2010 at 1:42 pm
    Hi Norman,
    So you are saying all of the datafiles are at the same SCN?
    If you took a hot backup, that would be possible if there was no db activity
    while it was going on, but even so maybe Oracle "knows" in some other way
    that it was an inconsistent backup (some kind of marker in the file headers)
    and needs to apply the next archive log number that was current during the
    backup. Since 9i rmans default action is to switch logfiles during the
    backup, so that changes going on during it are caught. If you do not have
    those logs, it may be hard to get the database open.

    Good luck

    Pete

    On Wed, Jan 27, 2010 at 1:11 PM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:
    I know there's a hidden parameter to force open a database but I'm not sure
    if it has the same effect on an RMAN recovery as it does on a non-RMAN
    recovery. I assume it would.

    The parameter is:

    NAME VALUE DEFLT TYPE DESCRIPTION
    _allow_resetlogs_corruption FALSE TRUE boolean allow resetlogs
    even if it will cause corruption


    There is also this one that might be of interest
    _allow_read_only_corruption FALSE TRUE boolean allow read-only
    open even if database is corrupt


    These are ONLY as a last resort to allow you access to your data ...


    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com


    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
    and may also be privileged. If you are not the named recipient, please
    notify the sender immediately and delete the contents of this message
    without disclosing the contents to anyone, using them for any purpose, or
    storing or copying the information on any medium.


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dunbar, Norman
    Sent: Wednesday, January 27, 2010 6:30 AM
    To: Martin Bach
    Cc: Niall Litchfield; ORACLE-L
    Subject: RE: Interesting RMAN duplicate problem.

    Morning Martin,
    I like MOS note "How to recover and open the database if the
    archivelog required for recovery is either missing, lost or
    corrupted? [ID
    465478.1]" which might be of help.
    Thanks. Yes, I had seen that one when researching this problem.
    Unfortunately, I get stumped by this bit in Option 2:

    "However, if we restored from an online or hot backup, then we may not
    be able to open the database, because we still need to apply the
    archivelogs generated during the said backup in order to synchronize the
    SCN of the datafiles before we can normally open the database."

    All my datafile headers are synchronised - which makes like
    "interesting".


    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else. We have
    checked this email and its attachments for viruses. But you should still
    check any attachment before opening it. We may have to make this message and
    any reply to it public if asked to under the Freedom of Information Act,
    Data Protection Act or for litigation. Email messages and attachments sent
    to or from any Environment Agency address may also be accessed by someone
    other than the sender or recipient, for business purposes. If we have sent
    you information and you wish to use it please read our terms and conditions
    which you can get by calling us on 08708 506 506. Find out more about the
    Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    http://www.freelists.org/webpage/oracle-l




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

    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Jan 27, 2010 at 1:42 pm
    Hi Chris,
    I know there's a hidden parameter to force open a database
    but I'm not sure if it has the same effect on an RMAN
    recovery as it does on a non-RMAN recovery. I assume it would.
    Yes, the document mentioned by Martin (MOS note 465478.1) mentions "a
    hidden parameter" that can be used - I suspect this is the very one.

    Unfortunately, I'd really like to open this database in a valid and
    no-potentially-corrupt manner. The users need a full sized testing
    database for performance testing of a new release (coming soon!) and if
    it has the possibility of being "corrupted" then it's a worthless
    database.

    Thanks anyway.

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Peter Hitchman at Jan 27, 2010 at 2:02 pm
    Hi,
    If you try the "recover database until cancel" command in sql*plus, what
    does it ask for?

    Also databases opened with the parameter _alow_resetlogs_corruption, Chris
    referenced, are usually OK for use, its just that you cannot back them up
    and restore them again, you have to re-create them, not an issue for you.

    Regards

    Pete
    On Wed, Jan 27, 2010 at 1:42 PM, wrote:

    Hi Chris,
    I know there's a hidden parameter to force open a database
    but I'm not sure if it has the same effect on an RMAN
    recovery as it does on a non-RMAN recovery. I assume it would.
    Yes, the document mentioned by Martin (MOS note 465478.1) mentions "a
    hidden parameter" that can be used - I suspect this is the very one.

    Unfortunately, I'd really like to open this database in a valid and
    no-potentially-corrupt manner. The users need a full sized testing
    database for performance testing of a new release (coming soon!) and if
    it has the possibility of being "corrupted" then it's a worthless
    database.

    Thanks anyway.

    Cheers,
    Norman.


    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else. We have
    checked this email and its attachments for viruses. But you should still
    check any attachment before opening it. We may have to make this message and
    any reply to it public if asked to under the Freedom of Information Act,
    Data Protection Act or for litigation. Email messages and attachments sent
    to or from any Environment Agency address may also be accessed by someone
    other than the sender or recipient, for business purposes. If we have sent
    you information and you wish to use it please read our terms and conditions
    which you can get by calling us on 08708 506 506. Find out more about the
    Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Jan 27, 2010 at 2:45 pm
    Ok. I wasn't sure what the purpose was of getting this db open from an old backup.

    Thanks,

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com


    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    -----Original Message-----
    From: Dunbar, Norman
    Sent: Wednesday, January 27, 2010 7:42 AM
    To: Taylor, Chris David
    Cc: ORACLE-L
    Subject: RE: Interesting RMAN duplicate problem.

    Hi Chris,
    I know there's a hidden parameter to force open a database
    but I'm not sure if it has the same effect on an RMAN
    recovery as it does on a non-RMAN recovery. I assume it would.
    Yes, the document mentioned by Martin (MOS note 465478.1) mentions "a
    hidden parameter" that can be used - I suspect this is the very one.

    Unfortunately, I'd really like to open this database in a valid and
    no-potentially-corrupt manner. The users need a full sized testing
    database for performance testing of a new release (coming soon!) and if
    it has the possibility of being "corrupted" then it's a worthless
    database.

    Thanks anyway.

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Dunbar, Norman at Jan 27, 2010 at 2:56 pm
    Hi Chris,
    Ok. I wasn't sure what the purpose was of getting this db
    open from an old backup.
    The purpose is (was) to get a full sized clone of production to do some
    testing and performance testing of a new "coming soon" release of the
    application.

    My own intention on using an older backup was to try and do a PITR clone
    rather than a fully up top date clone. It backfired on me and instead of
    saving 5 or 6 hours taking an rman backup and then cloning that, I
    wasted my time as the database won't open.

    Unfortunately, I've now blown away the original dump files and I'm about
    half way through a new rman backup ready to duplicate again.

    I'm sure that in the past I've had problems of a similar nature when
    cloning from a hot backup - even with all the archived logs available, I
    still got errors telling me that the system file would need more
    recovery to be consistent.

    Thanks everyone for input and suggestions.

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Peter Hitchman at Jan 27, 2010 at 2:55 pm
    Hi,
    Did you check to make sure that you are using a backup of the control file
    taken during the hot backup, I think this could mean you have a too recent
    copy of the control file.

    Regards

    Pete

    On Wed, Jan 27, 2010 at 2:12 PM, Dunbar, Norman <
    norman.dunbar_at_environment-agency.gov.uk> wrote:
    Hi Pete,
    If you try the "recover database until cancel" command in
    sql*plus, what does it ask for?
    A missing log file that I do not have - sequence 675041. This is higher
    than the range of sequences in the archivelog backup - 674565 - 674627.
    Also databases opened with the parameter
    _alow_resetlogs_corruption, Chris referenced, are usually OK
    for use, its just that you cannot back them up and restore
    them again, you have to re-create them, not an issue for you.
    Yes, an export takes 50 hours and an import takes a week. There are
    billions of rows in some tables, plus, a lot of IOTs. We did one just
    before christmas - it wasn't fun!

    Cheers,
    Norman.


    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else. We have
    checked this email and its attachments for viruses. But you should still
    check any attachment before opening it. We may have to make this message and
    any reply to it public if asked to under the Freedom of Information Act,
    Data Protection Act or for litigation. Email messages and attachments sent
    to or from any Environment Agency address may also be accessed by someone
    other than the sender or recipient, for business purposes. If we have sent
    you information and you wish to use it please read our terms and conditions
    which you can get by calling us on 08708 506 506. Find out more about the
    Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Peter Hitchman at Jan 27, 2010 at 3:08 pm
    Hi,
    Well you just check, as you suggest, on the timestamp of the files when they
    have restored and/or mount the database instance and check controlfile_time
    etc in v$database.

    On Wed, Jan 27, 2010 at 3:01 PM, Dunbar, Norman <
    norman.dunbar_at_environment-agency.gov.uk> wrote:
    Hi Peter,
    Did you check to make sure that you are using a backup of
    the control file taken during the hot backup, I think this
    could mean you have a too recent copy of the control file.
    At the risk of appearing a bit thick, my usual state, how would I check?

    The backup includes the control file always, and automgically when the
    system tablespace is backed up - so it's in the backup. I notice that
    the control files have today's date & time now - I assume that's come
    from my various attempts to recover the database?


    cheers,
    Norman.


    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051



    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else. We have
    checked this email and its attachments for viruses. But you should still
    check any attachment before opening it. We may have to make this message and
    any reply to it public if asked to under the Freedom of Information Act,
    Data Protection Act or for litigation. Email messages and attachments sent
    to or from any Environment Agency address may also be accessed by someone
    other than the sender or recipient, for business purposes. If we have sent
    you information and you wish to use it please read our terms and conditions
    which you can get by calling us on 08708 506 506. Find out more about the
    Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Marcin Przepiorowski at Jan 27, 2010 at 3:30 pm

    On Wed, Jan 27, 2010 at 2:12 PM, Dunbar, Norman wrote:
    Hi Pete,
    If you try the "recover database until cancel" command in
    sql*plus, what does it ask for?
    A missing log file that I do not have - sequence 675041. This is higher
    than the range of sequences in the archivelog backup - 674565 - 674627.
    Hi,

    Did you apply all these archive log between 674565 - 674627 ?
    Check alert log if all of those had been applied without errors.

    If restore process is asking about higher sequence there is a chance
    that control file is a newer than data files.

    Did you try with recreation of control file ?

    backup current control file to trace
    shutdown db
    copy current control file - just in case
    startup nomount
    use trace file to recreate control file with resetlog option

    Duplication process without until time restriction is trying to
    restore DB to current production state
    and if I remember correctly there was some issues related to that in
    9i - but most of them could be solved via control file recreation.

    regards,
    Marcin
  • Robert Freeman at Jan 27, 2010 at 4:26 pm
    Once in a while (especially in 9i) I would get weird problems with RMAN duplicate. My solution often was to do the following:

    Do the duplicate. It would fail as you describe.
    Start a manual recovery using the SQL*Plus recover database until cancel command. You can use until time, etc.
    Manually extract archived redo logs from the source system as required for the recovery on the duplicate.
    Once I'm at the point I want to open the database, I would open the database using resetlogs.

    The problem often involved a case where the recovery point-in-time was contained in an online redo log that was not archived yet (the current online redo log). You can, of course, use the V$ views (ie: V$RECOVER_FILE, V$DATAFILE, etc...) to determine the state of the data files, and their consistency.

    Cheers!

    RF

    Robert G. Freeman
    Oracle ACE
    Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
    Author:
    Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
    OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Other various titles
    Blog: http://robertgfreeman.blogspot.com
    Check out my new blog series on installing Oracle Database 11gR2 on Windows using VMWare!

    From: Marcin Przepiorowski
    To: norman.dunbar_at_environment-agency.gov.uk
    Cc: pjhoraclel_at_gmail.com; oracle-l
    Sent: Wed, January 27, 2010 8:30:15 AM
    Subject: Re: Interesting RMAN duplicate problem.

    On Wed, Jan 27, 2010 at 2:12 PM, Dunbar, Norman
    wrote:
    Hi Pete,
    If you try the "recover database until cancel" command in
    sql*plus, what does it ask for?
    A missing log file that I do not have - sequence 675041. This is higher
    than the range of sequences in the archivelog backup - 674565 - 674627.
    Hi,

    Did you apply all these archive log between 674565 - 674627 ?
    Check alert log if all of those had been applied without errors.

    If restore process is asking about higher sequence there is a chance
    that control file is a newer than data files.

    Did you try with recreation of control file ?

    backup current control file to trace
    shutdown db
    copy current control file - just in case
    startup nomount
    use trace file to recreate control file with resetlog option

    Duplication process without until time restriction is trying to
    restore DB to current production state
    and if I remember correctly there was some issues related to that in
    9i - but most of them could be solved via control file recreation.

    regards,
    Marcin
  • Dunbar, Norman at Jan 28, 2010 at 8:47 am
    Morning Robert,
    ...
    1. Do the duplicate. It would fail as you describe.
    2. Start a manual recovery using the SQL*Plus recover
    database until cancel command. You can use until time, etc.
    3. Manually extract archived redo logs from the source
    system as required for the recovery on the duplicate.
    It was going so well. The problem is a chunk of missing archived logs,
    so I'm unable to apply them. :-(
    4. Once I'm at the point I want to open the database, I
    would open the database using resetlogs.
    Which is, unfortunately, the point I can't get to. System data file
    needs additional recovery.
    The problem often involved a case where the recovery
    point-in-time was contained in an online redo log that was
    not archived yet (the current online redo log). You can, of
    course, use the V$ views (ie: V$RECOVER_FILE, V$DATAFILE,
    etc...) to determine the state of the data files, and their
    consistency.
    All files are online at the same SCN, date & Time according to
    v$datafile_header. All files are still in "RECOVERY" status according to
    V$RECOVER_FILE.

    I have the rman backup and the backup of all the archived logs from
    before, through and just after the backup of the datafiles.

    Well, I did have. I've blown them all away now and restarted the rman
    backup. Better luck this time I hope!

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Dunbar, Norman at Jan 28, 2010 at 8:51 am
    Morning Harel,
    When you tried to recover the database, did you specify
    "recover..... until cancel/time/scn"?
    I did this:

    run {
    set until time "....";
    duplicate target database to;
    }

    Rman restored the datafiles and then executed the following script all
    by itself:

    run {
    set until time "....";
    recover clone database
    delete archivelog ...;
    }

    :-(

    Cheers,
    Norman.

    Norman Dunbar
    Contract Oracle DBA
    CIS Engineering Services
    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 27, '10 at 10:40a
activeJan 28, '10 at 8:51a
posts17
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase