Recover from Cold Backup????
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Recover from Cold Backup????

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Posts
    6
    Hi World,
    I have a following question that make me very confusing, therefore I need your help.

    Question:
    I have a full backup (cold backup with shutdown before backup) on Sunday. DB run on archivelog mode. On Friday, the production machine is gone. Can I use the last backup (cold backup) and archive log files to recover point in time before accident?

    Thanks for your attention
    Xung

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Yup, that's the idea.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2001
    Posts
    6
    Hi Marist89,
    Thanks for your reply but I do not understand What do you mean?

    Xung

  4. #4
    Join Date
    Jul 2000
    Location
    Hong Kong
    Posts
    8
    Yes, you can recover your database with your last cold backup on Sunday and archive log files provided that you've got all the archived log files since your last cold backup.
    Ann (aka Darknight)
    OCP 8i,9i

  5. #5
    Join Date
    Jun 2002
    Posts
    73
    You can recover your database to point-in-time provided you have valid cold backup and all archived log files from the time of cold backup to the point of failure. Whenever any datafile get corrupted , you just copy that datafile from cold backup (if it's not system) , make the datafile offline and startup the database..Issue the command

    SQL>SET AUTORECOVERY ON;
    SQL>RECOVER DATAFILE 'DISK/DATA1.ORA' AUTOMATIC;

    if it's System datafile then you have to recover in mount mode.

    SQL>RECOVER DATABASE AUTO;
    You cannot succeed if you fear to face challenges.
    MAS
    BE(CS) , OCP 8/8i.



  6. #6
    Join Date
    Sep 2001
    Posts
    6
    Thanks for your answers.
    whole production is gone. I mean redo log files, datafiles, controlfiles are gone... There are only archivelog files and last cold backup. Can I recover to point in time?

    Xung

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I don't think so. Not without your old control files. Sounds like you can only recover to your last COLD BACKUP point. Since you are using your old CONTROL FILES, they won't recognise that a failure has occured and therefore will not initiate recovery. Confimation?
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    The old control files won't know diddly about anything after the cold backup, so I confirm grjohnson. That is, of course, assuming Xung made copies of the control files when he/she did the cold backup. No control files at all? Game over.

  9. #9
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Originally posted by Xung
    Thanks for your answers.
    whole production is gone. I mean redo log files, datafiles, controlfiles are gone... There are only archivelog files and last cold backup. Can I recover to point in time?

    Xung
    I too agree with Johnson and stecal. assuming your coldbackup includes the controlfiles, redolog files and datafiles, You can just restore them and startup your database. I guess even you apply recover database kind of commands, you will get something similar to "Media Recovery Not Required". kind of messages.

    I am not sure, but logically speaking, It may be possible through using backup controlfile clause to rolling the archivelogs forword. Please NOTE I have not tried this. If it works please let me know. I hope some of the DBA Guru's will speak on this.



    Nagesh

  10. #10
    Join Date
    Sep 2001
    Posts
    6
    Hi world,
    The Answer is "Yes". We can use old controlfile and roll forward with archivelog file. Following are some steps that I tested. It works actually!
    ***
    This test scenario demonstrates how to apply archivelog files to a clean but
    old backup.

    Let's first shut the database down in it's current situation:

    SVRMGR> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    We make a clean cold backup of the database; namely, all files,
    controlfiles, datafiles, etc. Then, start the database up again.

    Checking the Archive_Dest directory, we see that the last archivelog file for
    this test is 'arc84.1'. We'll come back to that later.

    SVRMGR> connect internal/password
    Connected.
    SVRMGR> startup
    ORACLE instance started.
    Total System Global Area 11907072 bytes
    Fixed Size 49152 bytes
    Variable Size 10657792 bytes
    Database Buffers 1126400 bytes
    Redo Buffers 73728 bytes
    Database mounted.
    Database opened.

    As a test we create a new table, insert some rows, and then make some
    logswitches:

    SVRMGR> create table testtable(a number(2));
    Statement processed.
    SVRMGR> insert into testtable values(2);
    1 row processed.
    SVRMGR> insert into testtable values(2);
    1 row processed.
    SVRMGR> insert into testtable values(2);
    1 row processed.
    SVRMGR> insert into testtable values(2);
    1 row processed.
    SVRMGR> insert into testtable values(2);
    1 row processed.
    SVRMGR> commit;
    Statement processed.
    SVRMGR> alter system switch logfile;
    Statement processed.
    SVRMGR> alter system switch logfile;
    Statement processed.
    SVRMGR> alter system switch logfile;
    Statement processed.
    SVRMGR> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    Again checking the Archive_Dest directory, we now see the last archivelog file
    is arc87.1. (Three onwards from the arc84.1 we had previously).

    We now intend to restore the backup we made earlier.

    The plan is to apply the three new archivelog files, so first we start the
    database up again:

    SVRMGR> connect internal/password
    Connected.
    SVRMGR> startup mount
    ORACLE instance started.
    Total System Global Area 11907072 bytes
    Fixed Size 49152 bytes
    Variable Size 10657792 bytes
    Database Buffers 1126400 bytes
    Redo Buffers 73728 bytes
    Database mounted.

    And now we issue the command to start the recovery and we apply all the
    archivelog files that are newer than any of the control or datafiles that we
    restored.

    SVRMGR> alter database recover until cancel using backup controlfile;
    alter database recover until cancel using backup controlfile
    *
    ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for thread 1
    ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
    ORA-00280: change 216252 for thread 1 is in sequence #85
    SVRMGR> alter database recover continue default;
    alter database recover continue default
    *
    ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1
    ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC86.1
    ORA-00280: change 216265 for thread 1 is in sequence #86
    ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for
    this recovery
    SVRMGR> alter database recover continue default;
    alter database recover continue default
    *
    ORA-00279: change 216266 generated at 04/09/99 16:45:40 needed for thread 1
    ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC87.1
    ORA-00280: change 216266 for thread 1 is in sequence #87
    ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC86.1' no longer needed for
    this recovery
    SVRMGR> alter database recover continue default;
    alter database recover continue default
    *
    ORA-00279: change 216267 generated at 04/09/99 16:46:00 needed for thread 1
    ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC88.1
    ORA-0028.
    0: change 216267 for thread 1 is in sequence #88
    ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC87.1' no longer needed for
    this recovery
    SVRMGR> alter database recover continue default;
    alter database recover continue default
    *
    ORA-00308: cannot open archived log 'D:\ORA80\DATABASE\ARCHIVE\ARC88.1'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.

    We could have stopped after 87 but there is no harm in continuing until the
    system cannot find the required file.

    Now we stop the recovery and open the database:

    SVRMGR> alter database recover cancel;
    alter database recover cancel
    Statement processed.
    SVRMGR> alter database open resetlogs;
    Statement processed.

    And, finally, we prove that the new information that was not in the backup
    and was only stored in the archivelog files is now recovered:

    SVRMGR> select * from testtable;
    A
    ----------
    2
    2
    2
    2
    2
    5 rows selected.
    SVRMGR>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width