Using Arcserve UDP Agent (Windows) › Perform a Restore › How to Restore an Oracle Database › Restore the Entire Database (Tablespaces and Control Files)

Restore the Entire Database (Tablespaces and Control Files)

You can restore all the entire database (all tablespaces and control files). Before you restore, you must locate the file. When you locate the files, ensure that the database is in the Open state. If the database is open, use the ALTER TABLESPACE. OFFLINE statement to take the tablespaces or datafiles offline before you begin the restore process.

Follow these steps:

1. Log in to the computer where you want to restore the tablespaces or datafiles.
2. Locate the user tablespaces or datafiles using the following command:

   SQL> SELECT TABLESPACE_NAME, FILE_NAME from DBA_DATA_FILES;
   

   SQL> SHOW PARAMETER CONTROL FILES;
   

3. Change the state of the database to nomount, or shutdown before you restore the tablespaces or datafiles.

   SQL> STARTUP NOMOUNT;
   

   SQL> SHUTDOWN IMMEDIATE;
   

4. Log in to the Arcserve UDP Console.
5. Restore the tablespaces or datafiles using the Restore Wizard. For more information on the restore process, see How to Restore From a Recovery Point.
6. Log in to the destination computer.
7. Navigate to the specific folders and verify that the tablespaces or datafiles are restored.
8. Recover the database.

   SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
   

9. Apply the prompted archived logs.

   Note: If the required archived log is missing, then it implies that a necessary redo record is located in the online redo logs. It occurs because unarchived changes are located in the online logs when the instance failed. You can specify the full path of an online redo log file and press Enter (you may have to try this a few times until you find the correct log).

10. Enter the following command to return the control file information about the redo log of a database:

    SQL>SELECT * FROM V$LOG;
    

11. (Optional) Enter the following command to see the names of all of the member of a group:

    SQL>SELECT * FROM V$LOGFILE;
    

    Example: After applying the prompted archived logs, you may see the following messages:

    ORA-00279: change 55636 generated at 24/06/2014 16:59:47 needed for thread 1
    

    ORA-00289: suggestion e:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG\2014_06_24\ O1_MF_1_2_9TKXGGG2_.ARC
    

    ORA-00280: change 55636 for thread 1 is in sequence #24
    

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    

12. Specify the full path of the online redo log file and press Enter.

    Example: E:\app\Administrator\oradata\orcl\redo01.log

    Note: You have to specify the full path multiple times until you get the correct log.

    The following messages are displayed:

    Log applied
    

    Media recovery complete
    

13. Open the database with the RESETLOGS clause after completing the recovery process.

    SQL> ALTER DATABASE OPEN RESETLOGS;
    

The entire database is restored.