Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unhappy Unanswered: Restore Online Backup (8.1.6/W2K)

    I continue to struggle with developing an effective online backup and restore process. I thought I had a viable backup process, but when I test the restore process, it will not allow me to open the database.

    I have the control file, the data files, the redo log files and all (I think all) the archived redo logs. Here are the steps that I am taking:

    1. Shut down the database.
    2. Copy everything in the \oradata\[SID] folder to another folder (just in case!)
    3. Copy my backed up files into the \oradata\[SID] folder
    4. Copy the archived redo log files into the \oradata\[SID]\archive folder
    5. Start svrmgr (svrmgrl from command line)
    6. SVRMGR > connect internal@[SID]
    7. SVRMGR > startup mount pfile=[location of pfile]

    up to this point everything seems fine; now it's time to recover the database

    8. SVRMGR > recover database using backup controlfile

    or:

    8a. SVRMGR > recover database until time [time] using backup controlfile

    The process rolls forward, but then asks me for a missing archived redo log file (it's always the next sequence number; e.g. if I have #001.arc and #002.arc, it wants #003.arc). When I undo the restore, shutdown the db and then copy over the files I had before I started the recovery process, I find that the "missing" file is the one that's "currently active" active (I match the sequence number in the redo log files).

    I have tried manually checkpointing the database before shutting it down (which results in the file with the right sequence number being deposited in the archived redo log folder). But even then, the recovery process does not "see" the file.

    What am I doing wrong?
    Why is this recovery process so difficult?
    Why is the recovery process looking for a file that's created even AFTER the time specified in my UNTIL TIME clause?

    I need a backup process that allows me to:
    1. Backup the database while keeping it on line
    2. Does not use RMAN
    3. Does not use OEM
    4. Will allow me to recover the database to a specific point in time (I'll take a specific sequence number if I can get it)

    frustrated.

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by hmscott

    I have the control file, the data files, the redo log files and all (I think all) the archived redo logs. Here are the steps that I am taking:

    3. Copy my backed up files into the \oradata\[SID] folder
    4. Copy the archived redo log files into the \oradata\[SID]\archive folder
    5. Start svrmgr (svrmgrl from command line)
    6. SVRMGR > connect internal@[SID]
    7. SVRMGR > startup mount pfile=[location of pfile]

    up to this point everything seems fine; now it's time to recover the database

    8. SVRMGR > recover database using backup controlfile

    or:

    8a. SVRMGR > recover database until time [time] using backup controlfile

    The process rolls forward, but then asks me for a missing archived redo log file (it's always the next sequence number; e.g. if I have #001.arc and #002.arc, it wants #003.arc). .....

    frustrated.

    hmscott
    OK hmscott,

    I guess you are trying to recover the database with the old control file. Do not copy the backedup control while restoring. only copy the datafiles and try to recover the database with the EXISTING control file (NOT THE OLD ONE WHICH YOU HAVE IN YOUR BACKUP LOCATION BUT THE ONE WHICH IS LOCATED AT controlfiles init.ora PARAMETER)

    The information about the archieved (and online too) redo logs are stored into the controlfile. If you use the old control file which does not have the recent information about the archieved redo logs, in this case, it will give you trouble.

    Thanks,
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Okay...I'll try that. However, what I am trying to rehearse for is a disaster -- ie the control file is not available.

    I did find an interesting item that worked: I simply pulled the "active" redo log from v$logfile and substituted it for the "missing" archived redo log file. That worked like a charm.

    Now I am working on backing up the database while transactions are ongoing in the background. Yeeesh.
    Have you hugged your backup today?

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by hmscott
    Okay...I'll try that. However, what I am trying to rehearse for is a disaster -- ie the control file is not available..
    In this case,

    (1) START THE INSTANCE (STARTUP NOMOUNT)
    (2) CREATE THE CONTROL FILE (CREATE CONTROLFILE...)
    (3) at this point you have your database mounted but inconsistent.
    (4) make it consistent by applying the offline redologs (i.e, RECOVER
    DATABASE...command)

    (5) open the database (ALTER DATABASE OPEN).
    you can also open it with RESETLOGS option incase if you do not have even
    online redo logs.

    Thanks
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    When I do (ALTER DATABASE OPEN RESETLOGS), I get an error indicating that SYSTEM.DBF needs further recovery. Why?

    Thanks,

    hmscott

    Quote Originally Posted by Hings
    In this case,

    (1) START THE INSTANCE (STARTUP NOMOUNT)
    (2) CREATE THE CONTROL FILE (CREATE CONTROLFILE...)
    (3) at this point you have your database mounted but inconsistent.
    (4) make it consistent by applying the offline redologs (i.e, RECOVER
    DATABASE...command)

    (5) open the database (ALTER DATABASE OPEN).
    you can also open it with RESETLOGS option incase if you do not have even
    online redo logs.

    Thanks
    Have you hugged your backup today?

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by hmscott
    When I do (ALTER DATABASE OPEN RESETLOGS), I get an error indicating that SYSTEM.DBF needs further recovery. Why?

    Thanks,

    hmscott
    list all the steps which you have executed in sequence..
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by hmscott
    When I do (ALTER DATABASE OPEN RESETLOGS), I get an error indicating that SYSTEM.DBF needs further recovery. Why?

    Thanks,

    hmscott
    one point, ........

    Meaning that it looks for more redo logs and not find.

    Try to INCOMPLETE recovery, then RESETLOGS will work.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Here is the batch file that generates the backup files. Note that it is generated dynamically so many of the comments are eliminated:

    Code:
    spool temp_bcp_res
    $SET backup=F:\ora\backup
    connect sys/change_on_install@ORCL
    $ECHO %DATE% %TIME% Started backup process on %COMPUTERNAME% for instance ORCL as sys  Local target is F:\ora\backup >> F:\ora\backup\ORCL200407191000.log                                            
    $ECHO %DATE% %TIME% Remote target is \\REMOTESRV\ORA_BACKUPS\LOCALSRV >> F:\ora\backup\ORCL200407191000.log                                                                                    
    $ECHO %DATE% %TIME% Retention period is 7 days.  Backup performed as %USERNAME%. >> F:\ora\backup\ORCL200407191000.log                                                                                 
    $MKDIR F:\ora\backup\ORCL\200407191000                                                                                                                                                                 
    $ECHO %DATE% %TIME% Begin copying archived redo log files >> F:\ora\backup\ORCL200407191000.log                                                                                                        
    $ECHO %DATE% %TIME% Begin erasing archived redo log files. >> F:\ora\backup\ORCL200407191000.log                                                                                                       
    $ECHO %DATE% %TIME% Completed erasing archived redo log files. >> F:\ora\backup\ORCL200407191000.log                                                                                                   
    alter tablespace DRSYS begin backup;                                                                                                                                                                    
    $copy E:\ORACLE\ORADATA\ORCL\DR01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                                 
    alter tablespace DRSYS end backup;                                                                                                                                                                      
    alter tablespace INDX begin backup;                                                                                                                                                                     
    $copy E:\ORACLE\ORADATA\ORCL\INDX01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                               
    alter tablespace INDX end backup;                                                                                                                                                                       
    alter tablespace RBS begin backup;                                                                                                                                                                      
    $copy E:\ORACLE\ORADATA\ORCL\RBS01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                                
    alter tablespace RBS end backup;                                                                                                                                                                        
    alter tablespace SYSTEM begin backup;                                                                                                                                                                   
    $copy E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                             
    alter tablespace SYSTEM end backup;                                                                                                                                                                     
    alter tablespace TEMP begin backup;                                                                                                                                                                     
    $copy E:\ORACLE\ORADATA\ORCL\TEMP01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                               
    alter tablespace TEMP end backup;                                                                                                                                                                       
    alter tablespace TOOLS begin backup;                                                                                                                                                                    
    $copy E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                              
    alter tablespace TOOLS end backup;                                                                                                                                                                      
    alter tablespace USERS begin backup;                                                                                                                                                                    
    $copy E:\ORACLE\ORADATA\ORCL\USERS01.DBF F:\ora\backup\ORCL\200407191000                                                                                                                              
    alter tablespace USERS end backup;                                                                                                                                                                      
    ALTER DATABASE BACKUP CONTROLFILE TO 'F:\ora\backup\ORCL\200407191000\controlfile.ctl' REUSE;                                                                                                          
    $COPY E:\ORACLE\ORADATA\ORCL\REDO01.LOG F:\ora\backup\ORCL\200407191000                                                                                                                               
    $COPY E:\ORACLE\ORADATA\ORCL\REDO02.LOG F:\ora\backup\ORCL\200407191000                                                                                                                               
    $COPY E:\ORACLE\ORADATA\ORCL\REDO03.LOG F:\ora\backup\ORCL\200407191000                                                                                                                               
    $ECHO %DATE% %TIME% Completed Local Backup Process >> F:\ora\backup\ORCL200407191000.log                                                                                                               
    $ECHO %DATE% %TIME% Removed hot backup flag. >> F:\ora\backup\ORCL200407191000.log                                                                                                                     
    $erase f:\ora\backupHOTSEM_ORCL
    spool off
    $ECHO %DATE% %TIME% Starting creation of restore file (restore.lst). >> F:\ora\backup\ORCL200407191000.log                                                                                             
    SPOOL F:\ora\backup\ORCL\200407191000\restore.lst                                                                                                                                                      
    SET ECHO OFF PAGESIZE 0 LINESIZE 200 FEEDBACK OFF VERIFY OFF
    SELECT 'COPY ' || substr(name,instr(name, '', 1, 4)+1) || ' ' || name from v$datafile;
    SELECT 'COPY ' || substr(name,instr(name, '', 1, 4)+1) || ' ' || name from v$tempfile;
    SELECT 'COPY ' || substr(member,instr(member, '', 1, 4)+1) || ' ' || member from v$logfile;
    SELECT 'COPY *.ctl ' || name from v$controlfile;
    spool off
    $ECHO %DATE% %TIME% Completed creation of restore file. >> F:\ora\backup\ORCL200407191000.log                                                                                                          
                               
    $IF EXIST F:\ora\backup\ORCL\200407121000 erase F:\ora\backup\ORCL\200407121000\*.* /Q                                                                                                                
    $IF EXIST F:\ora\backup\ORCL\200407121000 rmdir F:\ora\backup\ORCL\200407121000                                                                                                           
    $MKDIR \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407191000                            
    $COPY F:\ora\backup\ORCL\200407191000\*.* \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407191000                                                                         
    $ECHO %DATE% %TIME% Start remote file purge process.  Purge files older than 7 days. >> F:\ora\backup\ORCL200407191000.log                                                                             
    $IF EXIST \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407121000 erase \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407121000\*.* /Q                                                          
    $IF EXIST \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407121000 rmdir \\REMOTESRV\ORA_BACKUPS\LOCALSRV\ORCL\200407121000                                                                 
    $ECHO %DATE% %TIME% Completed Remote file purge process. >> F:\ora\backup\ORCL200407191000.log                                                                                                         
    $ECHO %DATE% %TIME% Completed backup for %ComputerName% >> F:\ora\backup\ORCL200407191000.log                                                                                                          
    EXIT
    Quote Originally Posted by Hings
    list all the steps which you have executed in sequence..
    Have you hugged your backup today?

Posting Permissions

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